快速查询表的说明
如下:
select top 1000
ROW_NUMBER() OVER (ORDER BY a.name) AS No,
a.name AS 表名,
CONVERT(NVARCHAR(100),isnull(g.[value],'-')) AS 说明
from
sys.tables a left join sys.extended_properties g
on (a.object_id = g.major_id AND g.minor_id = 0)
查询所有表名 和表信息
如下：
SELECT 表名 = d.name,--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,标识 = 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 'Y' else 'N' 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 'Y'else 'N' end,默认值 = isnull(e.text,''),字段说明 = isnull(g.[value],'')
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
order by a.id,a.colorder