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='数据库名';-- 替换为要查询的数据库名
标签:index,IDX,IDXC,数据库,object,生成,id,tt,字典 From: https://www.cnblogs.com/wuyabaibsd/p/17727224.html