Sqlserver 查询数据字典

Sqlserver 查询某张表的数据字典

   查询的表是登录账号下的所有表
   若有分库分账号权限，没有查询出对应的表，更换数据库的登录账号后再查询

SELECT
		ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS '序号',
		IC.COLUMN_NAME as '字段名',
		IC.DATA_TYPE as '数据类型',
		IC.CHARACTER_MAXIMUM_LENGTH AS '长度',
		IC.IS_NULLABLE AS '可空',
		OBJECT_DEFINITION( SC.DEFAULT_OBJECT_ID ) AS '默认值',
		CONVERT ( VARCHAR ( 100 ), SEP.VALUE ) AS '描述'
FROM
	SYS.TABLES AS T
	INNER JOIN SYS.COLUMNS SC ON SC.OBJECT_ID = T.OBJECT_ID
	INNER JOIN INFORMATION_SCHEMA.COLUMNS IC ON IC.TABLE_NAME = T.NAME 
	AND SC.NAME = IC.COLUMN_NAME
	LEFT JOIN SYS.EXTENDED_PROPERTIES SEP ON T.OBJECT_ID = SEP.MAJOR_ID 
	AND SC.COLUMN_ID = SEP.MINOR_ID 
	AND SEP.NAME = 'MS_DESCRIPTION' 
WHERE 
	**T.NAME = 'AIR_YB_XX' ---要查询的表名**
	AND SCHEMA_NAME( T.SCHEMA_ID ) = 'dbo' 
ORDER BY
	IC.ORDINAL_POSITION

查询数据库下的所有表和表注释

select  ROW_NUMBER() OVER(ORDER BY a.object_id) AS '序号',
		a.name AS '表名', 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,
       表说明= isnull(f.value,''),
       序号= a.colorder,
       字段名= a.name,
       列说明= isnull(g.[value],''),
       标识列= case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then 1 else 0 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 '是' else '否' 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 '是' else '否' end,
       默认值= isnull(e.text,'')
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 表名称 ASC,序号 ASC