将sql server中所有表的列信息显示出来（转）

正在作一个关于sql server数据库导入excel文件的程序，要读取数据库中的列的信息，从网上找了很多资料，终于总结出来比较理想的 sql语句，执行后返回的列分别是：表名、列名、列类型、列长度、列描述、是否主键，语句如下：

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

结果如图：

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/10294527/viewspace-122656/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/10294527/viewspace-122656/