欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

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

程序员文章站 2022-08-02 18:28:02
USE DBNAME --指定要生成数据字典的数据库 GO SELECT 表名= CASE WHEN a.colorder= 1 THEN d.name ELSE '' END, 表说明= CASE WHEN a.colorder= 1 THEN isnull( f.value, '' ) ELSE ......
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