MSSQL 查看表结构
WHEN col.colorder = 1 THEN
obj.name
ELSE
""
END AS 表名,
col.colorder AS 序号,
col.name AS 列名,
t.name AS 数据类型,
col.length AS 长度,
ISNULL(COLUMNPROPERTY(col.id, col.name, "Scale"), 0) AS 小数位数,
CASE
WHEN COLUMNPROPERTY(col.id, col.name, "IsIdentity") = 1 THEN
"√"
ELSE
""
END AS 标识,
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 = si.name
AND so.xtype = "PK"
WHERE sc.id = col.id
AND sc.colid = col.colid) THEN
"√"
ELSE
""
END AS 主键, --"" 外健,
CASE
WHEN col.isnullable = 1 THEN
"√"
ELSE
""
END AS 允许空,
ISNULL(comm.text, "") AS 默认值,
ISNULL(ep. [ value ], "") AS 说明
FROM dbo.syscolumns col
LEFT JOIN dbo.systypes t
ON col.xtype = t.xusertype
inner JOIN dbo.sysobjects obj
ON col.id = obj.id
AND obj.xtype = "U"
AND obj.status >= 0
LEFT JOIN dbo.syscomments comm
ON col.cdefault = comm.id
LEFT JOIN sys.extended_properties ep
ON col.id = ep.major_id
AND col.colid = ep.minor_id
AND ep.name = "MS_Description"
LEFT JOIN sys.extended_properties epTwo
ON obj.id = epTwo.major_id
AND epTwo.minor_id = 0
AND epTwo.name = "MS_Description"
WHERE obj.name = "XXX " --表名
ORDER BY col.colorder;
-- 另外一种,查询所有的
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,
字段说明 = isnull(G.[value],""),
标识 = Case When COLUMNPROPERTY( A.id,A.name,"IsIdentity")=1 Then "√"Else "" End,
主键 = Case When exists(SELECT 1 FROM sysobjects Where xtype="PK" and parent_obj=A.id and name in (
SELECT name FROM sysindexes WHERE indid in( SELECT indid FROM sysindexkeys WHERE id = A.id AND colid=A.colid))) then "√" 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,"")
FROM
syscolumns A
Left Join
systypes B
On
A.xusertype=B.xusertype
Inner Join
sysobjects D
On
A.id=D.id and D.xtype="U" and D.name<>"dtproperties"
Left Join
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
Left Join
sys.extended_properties F
On
D.id=F.major_id and F.minor_id=0
--where d.name="OrderInfo" --如果只查询指定表,加上此条件
Order By
A.id,A.colorder
- 上一篇: mysql工具percona-toolkit之安装
- 下一篇:没有了