SELECT
'表名' = e.[name],
'表说明' = f.[value],
'字段序号' = a.colorder,
'字段名' = a.[name],
'字段类型' = b.[name],
'字段长度' = COLUMNPROPERTY(a.id,a.name,'PRECISION'),
'占用字节数' = a.length,
'字段注释' = c.[value],
'小数位数' = isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0),
'是否允许空' = case when a.isnullable=1 then '√'else '' end,
'默认值' = d.[text],
'是否是主键' = 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
FROM syscolumns a
LEFT JOIN systypes b ON a.xusertype = b.xusertype
LEFT JOIN sys.extended_properties c ON a.id=c.major_id AND a.colid=c.minor_id
left join syscomments d on a.cdefault=d.id
left join sysobjects e on a.id=e.id and e.xtype='U' and e.[name]<>'dtproperties'
left JOIN sys.extended_properties f on e.id=f.major_id and f.minor_id=0 --AND f.[name]='MS_Description'--f.name为属性名字,一个表可以有多个属性，表的属性名字右键新建
WHERE a.id=OBJECT_ID('dbo.Member')
结果为:
