数据字典

sqlserver:

-- =============================================
-- 数据字典：业务表（含表说明、字段说明、位置、外键）
-- =============================================
SELECT *
INTO #tmp
FROM(
    -- 1. 所有业务表说明
    SELECT '表说明' AS 类别, obj.name AS 表名, CAST(ISNULL(tblExt.value, '') AS NVARCHAR(500)) AS 名称或说明, '' AS 数据类型, '' AS 长度精度, '' AS 可空, 0 AS 位置, '' AS 字段说明
    FROM sys.objects obj
         LEFT JOIN sys.extended_properties tblExt ON tblExt.major_id=obj.object_id AND tblExt.minor_id=0 AND tblExt.name='MS_Description'
    WHERE obj.type='U' AND obj.name NOT LIKE 'MS%' AND obj.name NOT LIKE 'spt_%' AND obj.name NOT LIKE 'dt%' AND obj.name NOT LIKE 'sys%' AND obj.schema_id=SCHEMA_ID('dbo')
    UNION ALL

    -- 2. 所有业务表字段（含位置 + 字段说明）
    SELECT '字段' AS 类别, obj.name AS 表名, col.name AS 名称或说明, typ.name AS 数据类型, CASE WHEN typ.name IN ('varchar', 'nvarchar', 'char', 'nchar') THEN CAST(col.max_length AS VARCHAR(10))
                                                                            WHEN typ.name IN ('decimal', 'numeric') THEN CAST(col.precision AS VARCHAR(10))+','+CAST(col.scale AS VARCHAR(10))ELSE '' END AS 长度精度, CASE col.is_nullable WHEN 1 THEN '是' ELSE '否' END AS 可空, col.column_id AS 位置, CAST(ISNULL(ext.value, '') AS NVARCHAR(500)) AS 字段说明 -- ★ 字段说明 ★
    FROM sys.objects obj
         INNER JOIN sys.columns col ON obj.object_id=col.object_id
         INNER JOIN sys.types typ ON col.user_type_id=typ.user_type_id
         LEFT JOIN sys.extended_properties ext ON ext.major_id=col.object_id AND ext.minor_id=col.column_id AND ext.name='MS_Description'
    WHERE obj.type='U' AND obj.name NOT LIKE 'MS%' AND obj.name NOT LIKE 'spt_%' AND obj.name NOT LIKE 'dt%' AND obj.name NOT LIKE 'sys%' AND obj.schema_id=SCHEMA_ID('dbo')
    UNION ALL

    -- 3. 所有业务表外键
    SELECT '外键' AS 类别, tp.name AS 表名, fk.name AS 名称或说明, cp.name+' → '+tr.name+'.'+cr.name AS 数据类型, '' AS 长度精度, '' AS 可空, 0 AS 位置, '' AS 字段说明
    FROM sys.foreign_keys fk
         INNER JOIN sys.foreign_key_columns fkc ON fk.object_id=fkc.constraint_object_id
         INNER JOIN sys.tables tp ON fkc.parent_object_id=tp.object_id
         INNER JOIN sys.columns cp ON fkc.parent_column_id=cp.column_id AND cp.object_id=fkc.parent_object_id
         INNER JOIN sys.tables tr ON fkc.referenced_object_id=tr.object_id
         INNER JOIN sys.columns cr ON fkc.referenced_column_id=cr.column_id AND cr.object_id=fkc.referenced_object_id
    WHERE tp.name NOT LIKE 'MS%' AND tp.schema_id=SCHEMA_ID('dbo'))s
SELECT CASE WHEN 位置>1 THEN '' ELSE 表名 END AS 表名, s.类别, s.名称或说明, s.数据类型, s.长度精度, s.可空, s.位置, s.字段说明
FROM #tmp s
WHERE s.表名 NOT IN ('BookFloorInformationTable_checkmachinesystem_SenseitDB', 'AggregatedCounter', 'Hash', 'List', 'JobQueue', 'JobParameter', 'qkddb', 'Set', 'State', 'Job','Counter')
ORDER BY s.表名, 位置, 类别 DESC, 名称或说明;
DROP TABLE #tmp

 

PolarDbx2.0:

 

-- =============================================
-- 数据字典：业务表（PolarDB-X 2.0 兼容版）
-- =============================================

-- 1. 所有业务表说明
SELECT 
    '表说明' AS 类别,
    TABLE_NAME AS 表名,
    COALESCE(TABLE_COMMENT, '') AS 名称或说明,
    '' AS 数据类型,
    '' AS 长度精度,
    '' AS 可空,
    0 AS 位置,
    '' AS 字段说明
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_TYPE = 'BASE TABLE'
    AND TABLE_NAME NOT LIKE 'MS%'
    AND TABLE_NAME NOT LIKE 'spt_%'
    AND TABLE_NAME NOT LIKE 'dt%'
    AND TABLE_NAME NOT LIKE 'sys%'
    AND TABLE_NAME NOT LIKE 'BookFloorInformationTable_checkmachinesystem_SenseitDB'
    AND TABLE_NAME NOT IN ('AggregatedCounter', 'Hash', 'List', 'JobQueue', 'JobParameter', 'qkddb', 'Set', 'State', 'Job', 'Counter')

UNION ALL

-- 2. 所有业务表字段（含位置 + 字段说明）
SELECT 
    '字段' AS 类别,
    TABLE_NAME AS 表名,
    COLUMN_NAME AS 名称或说明,
    DATA_TYPE AS 数据类型,
    CASE 
        WHEN DATA_TYPE IN ('varchar', 'char') THEN CAST(CHARACTER_MAXIMUM_LENGTH AS CHAR(10))
        WHEN DATA_TYPE IN ('decimal', 'numeric') THEN CONCAT(CAST(NUMERIC_PRECISION AS CHAR(10)), ',', CAST(NUMERIC_SCALE AS CHAR(10)))
        ELSE ''
    END AS 长度精度,
    CASE IS_NULLABLE WHEN 'YES' THEN '是' ELSE '否' END AS 可空,
    ORDINAL_POSITION AS 位置,
    COALESCE(COLUMN_COMMENT, '') AS 字段说明
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME NOT LIKE 'MS%'
    AND TABLE_NAME NOT LIKE 'spt_%'
    AND TABLE_NAME NOT LIKE 'dt%'
    AND TABLE_NAME NOT LIKE 'sys%'
    AND TABLE_NAME NOT LIKE 'BookFloorInformationTable_checkmachinesystem_SenseitDB'
    AND TABLE_NAME NOT IN ('AggregatedCounter', 'Hash', 'List', 'JobQueue', 'JobParameter', 'qkddb', 'Set', 'State', 'Job', 'Counter','a_2024','a_2022','a_2023','a_gz_20250507','distributedlock','t1_小学','t1_中学')

UNION ALL

-- 3. 所有业务表外键（PolarDB-X 2.0 可能不支持外键，此部分可能为空）
SELECT 
    '外键' AS 类别,
    TABLE_NAME AS 表名,
    CONSTRAINT_NAME AS 名称或说明,
    COLUMN_NAME AS 数据类型,
    '' AS 长度精度,
    '' AS 可空,
    0 AS 位置,
    '' AS 字段说明
FROM information_schema.KEY_COLUMN_USAGE
WHERE TABLE_SCHEMA = DATABASE()
    AND REFERENCED_TABLE_SCHEMA IS NOT NULL
    AND TABLE_NAME NOT LIKE 'MS%'
    AND TABLE_NAME NOT LIKE 'spt_%'
    AND TABLE_NAME NOT LIKE 'dt%'
    AND TABLE_NAME NOT LIKE 'sys%'
    AND TABLE_NAME NOT LIKE 'BookFloorInformationTable_checkmachinesystem_SenseitDB'
    AND TABLE_NAME NOT IN ('AggregatedCounter', 'Hash', 'List', 'JobQueue', 'JobParameter', 'qkddb', 'Set', 'State', 'Job', 'Counter','a_2024','a_2022','a_2023','a_gz_20250507','distributedlock','t1_小学','t1_中学')
ORDER BY 表名, 位置, 类别 DESC, 名称或说明;