MYSQL数据库解析SQL 表结构信息查询 含主外键、自增长
发布时间：2020-12-12 00:56:35 所属栏目：MySql教程 来源：网络整理
导读：《MYSQL数据库解析SQL 表结构信息查询 含主外键、自增长》要点： 本文介绍了MYSQL数据库解析SQL 表结构信息查询 含主外键、自增长，希望对您有用。如果有疑问，可以联系我们。 最近项目需要做什么数据字典,需要表结构信息.在网上看了许多关于表结构信息的查
|
《MYSQL数据库解析SQL 表结构信息查询 含主外键、自增长》要点：
在查询过滤中我们添加以下信息就ok了： AND g.class_desc = 'OBJECT_OR_COLUMN' 修改后的SQL如下： 代码如下: 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??? dbo.sysindexes si ??????????????????????????????????????? INNER JOIN dbo.sysindexkeys sik ON si.id = sik.id ????????????????????????????????????????????????????????????? AND si.indid = sik.indid ??????????????????????????????????????? INNER JOIN dbo.syscolumns sc ON sc.id = sik.id ????????????????????????????????????????????????????????????? AND sc.colid = sik.colid ??????????????????????????????????????? INNER JOIN dbo.sysobjects so ON so.name = so.name ????????????????????????????????????????????????????????????? AND so.xtype = 'PK' ??????????????????????????????? WHERE?? sc.id = a.id ??????????????????????????????????????? AND sc.colid = a.colid ) THEN '√' ????????????????? ELSE '' ???????????? END, ??????? 外键 = CASE WHEN tony.fkey IS NOT NULL ?????????????????????? AND tony.fkey = a.colid THEN '√' ????????????????? ELSE '' ???????????? END, ??????? 外键表 = CASE WHEN tony.fkey IS NOT NULL ??????????????????????? AND tony.fkey = a.colid THEN OBJECT_NAME(tony.fkeyid) ?????????????????? ELSE '' ????????????? END, ??????? 外键字段 = CASE WHEN tony.fkey IS NOT NULL ???????????????????????? AND tony.fkey = a.colid ??????????????????? THEN ( SELECT?? name ?????????????????????????? FROM???? syscolumns ?????????????????????????? WHERE??? colid = tony.fkey ??????????????????????????????????? AND id = tony.fkeyid ???????????????????????? ) ??????????????????? ELSE '' ?????????????? END, ??????? 类型 = b.name, ??????? 长度 = a.length, ??????? 精度 = COLUMNPROPERTY(a.id,'PRECISION'), ??????? 小数位数 = ISNULL(COLUMNPROPERTY(a.id,'Scale'),0), ??????? 允许空 = CASE WHEN a.isnullable = 1 THEN '√' ?????????????????? ELSE '' ????????????? END, ??????? 默认值 = ISNULL(e.text,''), ??????? 字段说明 = ISNULL(g.[value], ??????? 创建时间 = d.crdate, ??????? 更改时间 = CASE WHEN a.colorder = 1 THEN d.refdate ??????????????????? ELSE NULL ?????????????? END FROM??? dbo.syscolumns a ??????? LEFT JOIN dbo.systypes b ON a.xtype = b.xusertype ??????? INNER JOIN dbo.sysobjects d ON a.id = d.id ?????????????????????????????????????? AND d.xtype = 'U' ?????????????????????????????????????? AND d.status >= 0 ??????? LEFT JOIN dbo.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 ?????????????????????????????????????????????? AND g.class_desc = 'OBJECT_OR_COLUMN' ??????? LEFT JOIN sys.extended_properties f ON d.id = f.major_id ?????????????????????????????????????????????? AND f.minor_id = 0 ??????? LEFT JOIN sysobjects htl ON htl.parent_obj = d.id ??????????????????????????????????? AND htl.xtype = 'F' ??????? LEFT JOIN sysforeignkeys tony ON htl.id = tony.constid WHERE?? d.name = 'Address'? --这里输入包含表名称的条件 ORDER BY d.id, ??????? a.colorder 运行结果如图：
我不怎么喜欢它的“类型”信息,一般的varchar都会有长度信息,还有这个查询对于SQL 2012的新数据类型不支持,该SQL里面的嵌套查询比较多,于是我就自己重新写了一个SQL. 这里提醒大家尽量用INFORMATION_SCHEMA.XXX视图而不去用sys.XXX视图. 新的SQL如下： 代码如下: SELECT ?--OBJECT_ID(a.TABLE_SCHEMA + '.' + a.TABLE_NAME) AS [object_id], ??????? CASE WHEN a.ORDINAL_POSITION = 1 ???????????? THEN a.TABLE_SCHEMA + '.' + a.TABLE_NAME ???????????? ELSE '' ??????? END AS TABLE_NAME, ??????? CASE WHEN ( a.ORDINAL_POSITION = 1 ??????????????????? AND p1.value IS NOT NULL ????????????????? ) THEN p1.value ???????????? ELSE '' ??????? END AS TABLE_Description, ??????? a.COLUMN_NAME, ??????? CASE WHEN ( ( CHARINDEX('char',a.DATA_TYPE) > 0 ????????????????????? OR CHARINDEX('binary',a.DATA_TYPE) > 0 ??????????????????? ) ??????????????????? AND a.CHARACTER_MAXIMUM_LENGTH <> -1 ????????????????? ) ???????????? THEN a.DATA_TYPE + '(' ????????????????? + CAST(a.CHARACTER_MAXIMUM_LENGTH AS VARCHAR(4)) + ')' ???????????? WHEN ( ( CHARINDEX('CHAR',a.DATA_TYPE) > 0 ??????????????????? ) ??????????????????? AND a.CHARACTER_MAXIMUM_LENGTH = -1 ????????????????? ) THEN a.DATA_TYPE + '(max)' ???????????? WHEN ( CHARINDEX('numeric',a.DATA_TYPE) > 0 ) ???????????? THEN a.DATA_TYPE + '(' + CAST(a.NUMERIC_PRECISION AS VARCHAR(4)) ????????????????? + ',' + CAST(a.NUMERIC_SCALE AS VARCHAR(4)) + ')' ???????????? ELSE a.DATA_TYPE ??????? END AS COLUMN_TYPE, ??????? CASE WHEN c.IS_IDENTITY = 1 THEN 'YES' ???????????? ELSE 'NO' ??????? END AS IS_IDENTITY, ??????? a.IS_NULLABLE, ??????? CASE WHEN a.COLUMN_DEFAULT IS NULL THEN '' ???????????? ELSE a.COLUMN_DEFAULT ??????? END AS Default_Value, ??????? CASE WHEN p.value IS NULL THEN '' ???????????? ELSE p.value ??????? END AS [COLUMN_Description], ??????? CASE WHEN o.name IS? NULL THEN '' ???????????? ELSE '√' ??????? END AS Is_PrimaryKey, ??????? CASE WHEN f.parent_column_id IS NULL THEN '' ???????????? ELSE '√' ??????? END AS Is_Foreignkeys, ??????? CASE WHEN referenced_object_id IS NULL THEN '' ???????????? ELSE OBJECT_NAME(referenced_object_id) ??????? END AS Foreign_Table, ??????? CASE WHEN referenced_object_id IS NULL THEN '' ???????????? ELSE ( SELECT? name ??????????????????? FROM??? sys.columns ??????????????????? WHERE?? object_id = f.referenced_object_id ??????????????????????????? AND column_id = f.referenced_column_id ????????????????? ) ??????? END AS Foreign_key FROM??? INFORMATION_SCHEMA.COLUMNS a ??????? INNER JOIN sys.columns c ON OBJECT_ID(a.TABLE_SCHEMA + '.' ????????????????????????????????????????????? + a.TABLE_NAME) = c.OBJECT_ID ??????????????????????????????????? AND a.COLUMN_NAME = c.NAME ??????? LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE b ON a.TABLE_SCHEMA = b.TABLE_SCHEMA ?????????????????????????????????????????????????????????? AND a.TABLE_NAME = b.TABLE_NAME ?????????????????????????????????????????????????????????? AND a.COLUMN_NAME = b.COLUMN_NAME ??????? LEFT JOIN sys.sysobjects o ON o.name = b.CONSTRAINT_NAME ????????????????????????????????????? AND o.xtype = 'PK' ??????? LEFT JOIN sys.extended_properties p ON OBJECT_ID(a.TABLE_SCHEMA + '.' ???????????????????????????????????????????????????????? + a.TABLE_NAME) = p.major_id ?????????????????????????????????????????????? AND a.Ordinal_position = p.minor_id ?????????????????????????????????????????????? AND p.class_desc = 'OBJECT_OR_COLUMN' ??????? LEFT JOIN sys.extended_properties p1 ON OBJECT_ID(a.TABLE_SCHEMA + '.' ????????????????????????????????????????????????????????? + a.TABLE_NAME) = p1.major_id ??????????????????????????????????????????????? AND p1.minor_id = 0 ??????? LEFT JOIN SYS.foreign_key_columns f ON OBJECT_ID(a.TABLE_SCHEMA + '.' ???????????????????????????????????????????????????????? + a.TABLE_NAME) = f.parent_object_id ?????????????????????????????????????????????? AND a.ORDINAL_POSITION = f.parent_column_id WHERE?? a.TABLE_NAME = 'Address' -- a.TABLE_NAME IN (SELECT name FROM sys.tables) ORDER BY a.TABLE_SCHEMA,a.TABLE_NAME,a.ORDINAL_POSITION 运行效果如图：
有不对的地方还请大家拍砖!谢谢! （编辑：李大同） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
