常用SQL語句，sql語句

常用SQL語句，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=0where b.name is not null order by d.name --,a.id,a.colorder