USE DBNAME --指定要生成数据字典的数据库 Go SELECT 表名= CASE WHEN a.colorder= 1 THEN d.name ELSE "" END, 表说明= CASE WH
USE DBNAME --指定要生成数据字典的数据库
Go
SELECT
表名=
CASE
WHEN a.colorder= 1 THEN
d.name ELSE ""
END, 表说明=
CASE
WHEN a.colorder= 1 THEN
isnull( f.value, "" ) ELSE ""
END, 字段序号= a.colorder, 字段名= a.name, 标识=
CASE
WHEN COLUMNPROPERTY( a.id, a.name, "IsIdentity" ) = 1 THEN
"√" ELSE ""
END, 主键=
CASE
WHEN EXISTS (
SELECT
1
FROM
sysobjects
WHERE
xtype = "PK"
AND name IN (
SELECT name FROM sysindexes WHERE indid IN (
SELECT indid FROM sysindexkeys WHERE id = a.id AND colid = a.colid ) )
) THEN
"√" ELSE ""
END,
类型= b.name,
占用字节数= a.length,
长度= COLUMNPROPERTY( a.id, a.name, "PRECISioN" ),
小数位数= isnull( COLUMNPROPERTY( a.id, a.name, "Scale" ), 0 ),
允许空=CASE
WHEN a.isnullable= 1 THEN
"√" ELSE ""
END,
默认值= isnull( e.text, "" ),
字段说明= isnull( g.[value], "" )
FROM
syscolumns a
LEFT JOIN systypes b ON a.xtype= b.xusertype
INNER JOIN sysobjects d ON a.id= d.id
AND d.xtype= "U"
AND d.name<> "dtproperties"
LEFT JOIN syscomments e ON a.cdefault= e.id
LEFT JOIN sys.extended_properties g ON a.id= g.major_id
AND a.colid= g.minor_id
LEFT JOIN sys.extended_properties f ON d.id= f.major_id
AND f.minor_id = 0
--where d.name="要查询的表" --如果只查询指定表,加上此条件
ORDER BY
a.id,
a.colorder
--结束END--
本文标题: 使用SQL生成指定数据库的数据字典(MSSQL)
本文链接: https://www.lsjlt.com/news/2764.html(转载时请注明来源链接)
有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341
下载Word文档到电脑,方便收藏和打印~
2024-05-16
2024-05-16
2024-05-16
2024-05-15
2024-05-15
2024-05-15
2024-05-15
2024-05-15
2024-05-15
2024-05-15
回答
回答
回答
回答
回答
回答
回答
回答
回答
回答
0