解析SQL 表结构信息查询 含主外键、自增长

这篇文章主要为大家详细介绍了解析SQL 表结构信息查询 含主外键、自增长，具有一定的参考价值，可以用来参考一下。

对此感兴趣的朋友，看看idc笔记做的技术笔记！

代码如下:

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, a.name, 'PRECISION') ,
 小数位数 = ISNULL(COLUMNPROPERTY(a.id, a.name, '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

代码如下:

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
 OR CHARINDEX('binary', 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

注：关于解析SQL 表结构信息查询 含主外键、自增长的内容就先介绍到这里，更多相关文章的可以留意