快速查看表结构 SQL server查看表注释以及字段注释表结构字段说明

declare @tablename nvarchar(max);
set @tablename = n'userintegralexchange';   --表名!!!

select case when col.colorder = 1 then obj.name
else ''
end as 表名 ,
col.colorder as 序号 ,
col.name as 列名 ,
isnull(ep.[value], '') 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 默认值
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;