posts - 10, comments - 3, trackbacks - 0, articles - 0

SqlServer 获取表信息

Posted on 2008-12-30 17:20 石头 阅读(919) 评论(1)  编辑 收藏 引用 所属分类: 数据库技术
项目快结束了,需要对数据库写个文档,包括表的主键,类型,描述等
网上查了好多,感觉都不爽,于是查阅Sql帮助文档,自己写了一个,感觉还可以,所以共享出来供大家参考:
   SELECT row_number()over(order BY t1.字段代码)序号, t1.字段代码,t1.字段名称,t1.类型,isnull(t2.主键,''), t1.可空,t1.备注 FROM
(SELECT c.name 字段代码,value 字段名称, systypes.name 类型,case c.isnullable when 0 then '否' else '是' end  可空, c.length 长度,value 备注
FROM systypes,sys.sysobjects o,syscolumns c
LEFT JOIN ::fn_listextendedproperty(N'MS_Description', N'user', N'dbo', N'table',@tableName, N'column', default) d
ON objname = c.name COLLATE Chinese_PRC_CI_AS
WHERE  c.xusertype = systypes.xusertype 
AND c.id = o.id
AND o.name =@tableName) t1
LEFT JOIN
(SELECT i.name 主键名称,c.name 主键
FROM sys.indexes i,sys.sysindexkeys sd, sys.all_columns c, sys.sysobjects o
WHERE i.index_id = sd.indid
AND i.object_id = o.id
AND i.is_primary_key = 1
AND sd.id = o.id
AND c.object_id = o.id
AND c.column_id = sd.colid
AND o.xtype='u'
AND o.name =@tableName) t2 ON t1.字段代码 = t2.主键
ORDER BY t1.字段代码

Feedback

# re: SqlServer 获取表信息  回复  更多评论   

2009-01-01 22:32 by oil painting
谢谢分享
只有注册用户登录后才能发表评论。