Posted on 2008-12-30 17:20
石头 阅读(1049)
评论(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.字段代码