查询表结构sql
时间：2021-07-01 10:21:17
帮助过：20人阅读
SELECT CASE WHEN col.colorder
= 1 THEN obj.name
ELSE ‘‘ END AS 表名, col.colorder
AS 序号, col.name
AS 列名,
2 ISNULL(ep.value, N
‘‘)
AS 列说明, t.name
AS 数据类型, col.length
AS 长度,
ISNULL(
COLUMNPROPERTY(col.id, col.name,
3 ‘Scale‘),
0)
AS 小数位数,
CASE WHEN COLUMNPROPERTY(col.id, col.name,
‘IsIdentity‘)
4 = 1 THEN ‘√‘ ELSE ‘‘ END AS 标识,
CASE WHEN EXISTS
5 (
SELECT 1
6 FROM dbo.sysindexes si
INNER JOIN
7 dbo.sysindexkeys sik
ON si.id
= sik.id
AND si.indid
= sik.indid
INNER JOIN
8 dbo.syscolumns sc
ON sc.id
= sik.id
AND sc.colid
= sik.colid
INNER JOIN
9 dbo.sysobjects so
ON so.name
= si.name
AND so.xtype
= ‘PK‘
10 WHERE sc.id
= col.id
AND sc.colid
= col.colid)
THEN ‘√‘ ELSE ‘‘ END AS 主键,
11 CASE WHEN col.isnullable
= 1 THEN ‘√‘ ELSE ‘‘ END AS 允许空,
ISNULL(comm.
text, N
‘‘)
AS 默认值
12 FROM syscolumns
AS col
LEFT OUTER JOIN
13 systypes
AS t
ON col.xtype
= t.xusertype
INNER JOIN
14 sysobjects
AS obj
ON col.id
= obj.id
AND obj.xtype
= ‘U‘ AND obj.status
>= 0 LEFT OUTER JOIN
15 syscomments
AS comm
ON col.cdefault
= comm.id
LEFT OUTER JOIN
16 sys.extended_properties
AS ep
ON col.id
= ep.major_id
AND col.colid
= ep.minor_id
AND
17 ep.name
= ‘MS_Description‘ LEFT OUTER JOIN
18 sys.extended_properties
AS epTwo
ON obj.id
= epTwo.major_id
AND epTwo.minor_id
= 0 AND
19 epTwo.name
= ‘MS_Description‘
20 WHERE (obj.name
= ‘TB_ROOM_STATE‘)
21 ORDER BY 序号
查询表结构sql
标签：object text where state outer scom desc code 默认值
