sqlserver生成数据字典:
--SQL语句构建表的数据字典
USE MyDatabaseName; -- 替换为您要查询的数据库名
SELECT 表名=CASE WHEN tt.column_id=1 THEN is_name ELSE N'' END,
字段序号=tt.column_id,
列名称=is_cname,
字段描述=ISNULL(is_value,N''),
主键=ISNULL(tt.PrimaryKey,N''),
是否递增=CASE WHEN tt.is_identity=1 THEN N'√'ELSE N'' END,
是否计算=CASE WHEN tt.is_computed=1 THEN N'√'ELSE N'' END,
数据类型=tt.name,
最大长度=tt.max_length,
数据长度=tt.precision,
小数长度=tt.scale,
允许空值=CASE WHEN tt.is_nullable=1 THEN N'√'ELSE N'' END,
默认值=ISNULL(tt.definition,N''),
索引名称=ISNULL(tt.IndexName,N''),
索引类型=ISNULL(tt.Sort,N''),
创建日期=tt.Create_Date,
修改日期=tt.Modify_date
FROM (
INFORMATION_SCHEMA.TABLES t
INNER JOIN INFORMATION_SCHEMA.COLUMNS c ON t.TABLE_NAME = c.TABLE_NAME AND t.TABLE_TYPE = 'BASE TABLE'
INNER JOIN (
SELECT O.name AS is_name,PTB.value,C.column_id,C.name AS is_cname,PFD.value AS is_value,IDX.PrimaryKey,C.is_identity,C.is_computed,T.name,C.max_length,C.precision,C.scale,
C.is_nullable,D.definition,IDX.IndexName,IDX.Sort,O.Create_Date,O.Modify_date
FROM sys.columns C
INNER JOIN sys.objects O ON C.[object_id]=O.[object_id] AND O.type='U' AND O.is_ms_shipped=0
INNER JOIN sys.types T ON C.user_type_id=T.user_type_id
LEFT JOIN sys.default_constraints D ON C.[object_id]=D.parent_object_id AND C.column_id=D.parent_column_id AND C.default_object_id=D.[object_id]
LEFT JOIN sys.extended_properties PFD ON PFD.class=1 AND C.[object_id]=PFD.major_id AND C.column_id=PFD.minor_id
LEFT JOIN sys.extended_properties PTB ON PTB.class=1 AND PTB.minor_id=0 AND C.[object_id]=PTB.major_id
LEFT JOIN (
SELECT IDXC.[object_id],IDXC.column_id,Sort=CASE INDEXKEY_PROPERTY(IDXC.[object_id],IDXC.index_id,IDXC.index_column_id,'IsDescending')
WHEN 1 THEN 'DESC' WHEN 0 THEN 'ASC' ELSE '' END,PrimaryKey=CASE WHEN IDX.is_primary_key=1 THEN N'√'ELSE N'' END,IndexName=IDX.Name
FROM sys.indexes IDX
INNER JOIN sys.index_columns IDXC ON IDX.[object_id]=IDXC.[object_id] AND IDX.index_id=IDXC.index_id
LEFT JOIN sys.key_constraints KC ON IDX.[object_id]=KC.[parent_object_id] AND IDX.index_id=KC.unique_index_id
INNER JOIN (
SELECT [object_id], Column_id, index_id=MIN(index_id)
FROM sys.index_columns
GROUP BY [object_id], Column_id
) IDXCUQ ON IDXC.[object_id]=IDXCUQ.[object_id] AND IDXC.Column_id=IDXCUQ.Column_id AND IDXC.index_id=IDXCUQ.index_id
) IDX ON C.[object_id]=IDX.[object_id] AND C.column_id=IDX.column_id
) tt on c.TABLE_NAME = tt.is_name AND tt.is_cname = c.COLUMN_NAME
)
ORDER BY is_name,tt.column_id ASC生成结果如下图, 直接复制或保存成excel文件即可:
