SQL sever查询出所有表名&导出数据字典

一、怎么用Sql语句获取一个数据库中的所有表的名字及描述

SELECT a.name, b.value
FROM sys.all_objects a
    LEFT JOIN sys.extended_properties b ON a.object_id = b.major_id
WHERE a.type = 'U'
    AND b.minor_id = 0
ORDER BY a.name
// sys.all_objects中保bai存了该库du中所有对zhi象的信息，daosys.extended_properties中保存了该库中虽有专对象的扩展属性属信息。
// a.type='U' 筛选出表
// b.minor_id = 0 筛选出仅是表的属性，而不是字段的属性

二、 导出数据字典

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
ORDER BY a.id, a.colorder

结束！