根据数据库表生成数据字典

sql server数据库

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,
       允许空值=CASE WHEN tt.is_nullable=1 THEN N'√'ELSE N'' END,
       默认值=ISNULL(tt.definition,N''),
       索引名称=ISNULL(tt.IndexName,N''),
       索引类型=ISNULL(tt.Sort,N'')
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;

mysql 数据库

SELECT
    t.TABLE_NAME AS '表名',
    t.COLUMN_NAME AS '字段名称',
    t.COLUMN_COMMENT AS '字段描述',
    CASE t.COLUMN_KEY WHEN 'PRI' THEN '√' ELSE ' ' END AS '主键',
    t.COLUMN_TYPE AS '数据类型',
    t.CHARACTER_OCTET_LENGTH AS '最大长度(byte)',
    CASE IFNULL(t.COLUMN_DEFAULT,'Null') 
        WHEN '' THEN '空字符串' 
        WHEN 'Null' THEN 'NULL' 
        ELSE t.COLUMN_DEFAULT END  AS '默认值',
    CASE t.IS_NULLABLE WHEN 'YES' THEN '是' ELSE '否' END AS '是否允许为空'
FROM information_schema.COLUMNS t 
WHERE t.TABLE_SCHEMA='数据库名';-- 替换为要查询的数据库名