常用SQL语句

1.查询数据库表结构

select 
       (case when a.colorder=1 then d.name else null end) [表名],  
       a.colorder [序号],
       a.name [字段名],
       (case when columnproperty(a.id,a.name,'IsIdentity')=1 then '√'else '' end) [标识], 
       (case when (select count(id) 
                     from sysobjects  
					where (name in (select name 
					                  from sysindexes  
                                     where (id = a.id) 
                                       and (indid in (select indid 
                                                        from sysindexkeys  
                                                       where (id = a.id) 
                                                         and (colid in (select colid 
                                                                          from syscolumns 
                                                                          where (id = a.id) 
                                                                            and (name = a.name)))))))  
                      and (xtype = 'PK'))>0 
                 then '√' else '' 
         end) [主键],
       b.name [类型],
       a.length [占用字节数],
       columnproperty(a.id,a.name,'PRECISION') [长度],  
      isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0) [小数位数],
      (case when a.isnullable=1 then '√'else '' end) [允许空],  
      isnull(e.text,'') [默认值],
      isnull(g.[value], ' ') [备注]
 from syscolumns a 
 left join systypes b 
   on a.xtype=b.xusertype  
 inner join sysobjects d 
   on a.id=d.id 
  and d.xtype='U' 
  and d.name not in ('dtproperties','sysdiagrams')
 left join syscomments e
   on a.cdefault=e.id  
 left join sys.extended_properties g 
   on a.id=g.major_id 
  and a.colid=g.minor_id
 left join sys.extended_properties f on d.id=f.class and f.minor_id=0
where b.name is not null 
order by d.name --,a.id,a.colorder