SQL server查询某张表对应的字段及字段类型

declare @tablename nvarchar(30)
set @tablename='pu_appvouch ’ --输入表名称

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