使用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