ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 将sql server中所有表的列信息显示出来(转)

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

原创 Linux操作系统 作者:jcszjswkzhou 时间:2019-06-22 14:21:06 0 删除 编辑
正在作一个关于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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2007-08-29

  • 博文量
    3488
  • 访问量
    2643058