SQL server 和 Mysql 使用T-SQL语句直接生成数据字典

1. SQL server生成数据字典:

--SQL语句构建表的数据字典
USE 数据库名;  -- 替换为您要查询的数据库名

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,
       最大长度=tt.max_length,
       数据长度=tt.precision,
       小数长度=tt.scale,
       允许空值=CASE WHEN tt.is_nullable=1 THEN N'√'ELSE N'' END,
       默认值=ISNULL(tt.definition,N''),
       索引名称=ISNULL(tt.IndexName,N''),
       索引类型=ISNULL(tt.Sort,N''),
       创建日期=tt.Create_Date,
       修改日期=tt.Modify_date
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

  生成结果如下图, 直接复制或保存成excel文件即可:

 

2. Mysql生成数据字典

SELECT
    t.TABLE_NAME AS '表名',
		t.ORDINAL_POSITION 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='数据库名'; /*替换为您要查询的数据库名*/

  生成结果如下图: