使用SQL生成指定数据库的数据字典(MSSQL)

use dbname --指定要生成数据字典的数据库

go
select
	表名=
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 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, '' ), 
	字段说明= isnull( g.[value], '' ) 
from
	syscolumns a
	left join systypes b on a.xtype= 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 
--where d.name='要查询的表' --如果只查询指定表,加上此条件
order by
	a.id,
	a.colorder