将SQL Server中所有表的列信息显示出来

1　select sysobjects.name as tb_name, syscolumns.name as col_name, systypes.name as col_type, syscolumns.length as col_len, isnull(sysproperties.value,syscolumns.name) as col_memo,
2　case when syscolumns.name in
3　(select 主键=a.name
4　from syscolumns a
5　inner join sysobjects b on a.id=b.id and b.xtype='u' and b.name<>'dtproperties'
6　where exists(select 1 from sysobjects where xtype='pk' and name in (
7　select name from sysindexes where indid in(
8　select indid from sysindexkeys where id = a.id and colid=a.colid
9　)))
10　and b.name=sysobjects.name
11　)
12　then 1 else 0 end as is_key
13
14　from sysobjects,systypes,syscolumns
15　left join sysproperties on (syscolumns.id = sysproperties.id and
16　syscolumns.colid = sysproperties.smallid)
17
18　where (sysobjects.xtype ='u' or sysobjects.xtype ='v')
19　and sysobjects.id = syscolumns.id and systypes.xtype = syscolumns.xtype
20　and systypes.name <> 'sysname' and sysobjects.name like '%' order by sysobjects.name, syscolumns.colid

结果如图：