查询表结构明细信息代码,MSSQL代码库,德仔网

代码语言

代码分类

【MSSQL】查询表结构明细信息

作者:Dezai.CN / 发布于2013/3/26/ 728


	SELECT TOP (100) PERCENT d.name AS 表名, a.id AS 表编号, a.colorder AS 字段序号,
	 a.colid AS 字段说明字段序号, a.name AS 字段名,
	 CASE WHEN COLUMNPROPERTY(a.id, a.name, 'IsIdentity') = 1 THEN '&radic;' ELSE '' END AS 标识,
	 (CASE WHEN EXISTS (SELECT 1
	 FROM sysobjects
	 WHERE xtype = 'PK'
	 AND name IN
	 (SELECT name
	 FROM sysindexes
	 WHERE indid IN
	 (SELECT indid
	 FROM sysindexkeys
	 WHERE id = a.id AND colid = a.colid)))
	 THEN '&radic;' ELSE '' END) AS 主键,
	 b.name AS 类型, a.length AS 占用字节数,
	 COLUMNPROPERTY(a.id, a.name, 'PRECISION') AS 长度,
	 ISNULL(COLUMNPROPERTY(a.id, a.name, 'Scale'), 0) AS 小数位数,
	 (CASE WHEN a.isnullable = 1 THEN '&radic;' ELSE '' END) AS 允许空,
	 ISNULL(e.text, '') AS 默认值, ISNULL(g.value, '') AS 系统字段说明
	FROM sys.syscolumns AS a LEFT OUTER JOIN
	 sys.systypes AS b ON a.xtype = b.xusertype INNER JOIN
	 sys.sysobjects AS d ON a.id = d.id AND d.xtype = 'U' AND d.name <> 'dtproperties' LEFT OUTER JOIN
	 sys.syscomments AS e ON a.cdefault = e.id LEFT OUTER JOIN
	 sys.extended_properties AS g ON a.id = g.major_id AND a.colid = g.minor_id
	ORDER BY 表名, 字段序号

试试其它关键字

　表结构明细　

同语言下

可能有用的

Dezai.CN贡献的其它代码(4037)