--快速查看表结构(字段、数据类型及字段说明)
declare @tablename nvarchar(30)
set @tablename="mapelement" --输入表名称
SELECT col.colorder AS 序号,
col.name AS 列名,
case when col.xtype=240 then "geography"
when t.name="nvarchar" then t.name+"("+convert(nvarchar(10),col.length/2)+")"
when t.name="varchar" then t.name+"("+convert(nvarchar(10),col.length/2)+")"
when t.name="decimal" then t.name+"("+convert(nvarchar(10),col.xprec)+","+convert(nvarchar(10),col.xscale)+")"
else t.name
end AS 数据类型,
col.length 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 =@tablename--表名
ORDER BY col.colorder;