园龄：5年3个月粉丝：63 关注：0

使用SQL生成指定数据库的数据字典(MSSQL)

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