根据库中的表自动生成数据字典的T-SQL脚本

一位QQ群的朋友发的脚本，可以根据数据库中的表生成数据字典,很实用！

稍作修改后收藏到这里：

CREATE TABLE TableTmp(ID int NOT NULL IDENTITY,

COL1 sysname,COL3 sysname,COL4 sysname,

COL5 sysname,COL6 sysname,COL7 sysname,COL8 sysname,

COL9 sysname)

DECLARE c CURSOR FAST_FORWARD FOR SELECT name FROM sys.tables ORDER BY name ASC

DECLARE @TbName sysname

OPEN c

FETCH NEXT FROM c INTO @TbName

WHILE @@FETCH_STATUS=0

BEGIN

INSERT INTO TableTmp(COL1,COL3,COL4,COL5,COL6,COL7,COL8,COL9)

SELECT '表名','','','','','','',''

UNION ALL

SELECT @TbName AS 表名,'','','','','','',''

INSERT INTO TableTmp(COL1,COL3,COL4,COL5,COL6,COL7,COL8,COL9)

SELECT '列名' AS 列名,'类型定义' AS 类型定义,'是否为空' AS 是否为空,

'是否标识' AS 是否标识,'标识定义' AS 标识定义,'默认定义' AS 默认定义,'是否主键' AS 是否主键,

'备注' AS 备注

INSERT INTO TableTmp(COL1,COL3,COL4,COL5,COL6,COL7,COL8,COL9)

SELECT c.name AS 列名,

CASE WHEN p.name='numeric' THEN p.name+'('+CAST(c.precision AS VARCHAR)+','+CAST(c.scale AS VARCHAR)+')'

WHEN p.name='varchar' THEN p.name+'('+CAST(c.max_length AS VARCHAR)+')'

ELSE p.name

END AS 类型定义,

CASE WHEN c.is_nullable=1 THEN 'NOT NULL'

ELSE 'NULL'

END AS 是否为空,

CASE WHEN c.is_identity=1 THEN 'Yes'

ELSE 'No'

END AS 是否标识,

CASE WHEN c.is_identity=1 THEN '('+CAST(i.seed_value AS VARCHAR)+','+CAST(i.increment_value AS VARCHAR)+')'

ELSE ''

END AS 标识定义,

CASE WHEN d.definition IS NOT NULL THEN SubString(d.definition,2,Len(d.definition)-2)

ELSE ''

END AS 默认定义,

CASE WHEN k.COLUMN_NAME IS NOT NULL THEN 'Yes'

ELSE 'No'

END AS 是否主键,

'' AS 备注

FROM sys.columns AS c

INNER JOIN sys.tables AS t ON c.object_id=t.object_id

INNER JOIN sys.types AS p ON c.user_type_id=p.user_type_id

LEFT JOIN sys.default_cONstraints AS d ON c.default_object_id=d.object_id

LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS k ON t.name=k.TABLE_NAME AND c.column_id=k.ORDINAL_POSITION AND k.CONSTRAINT_NAME LIKE 'PK%'

LEFT JOIN sys.identity_columns AS i ON c.object_id=i.object_id AND c.column_id=i.column_id

WHERE t.name=@TbName

ORDER BY c.column_id ASC

INSERT INTO TableTmp(COL1,COL3,COL4,COL5,COL6,COL7,COL8,COL9)

SELECT '','','','','','','',''

FETCH NEXT FROM c INTO @TbName

END

CLOSE c

DEALLOCATE c

SELECT COL1,COL3,COL4,COL5,COL6,COL7,COL8,COL9 FROM TableTmp ORDER BY ID ASC

TRUNCATE TABLE TableTmp

DROP TABLE TableTmp