数据库字典查询SQL

最新推荐文章于 2024-05-17 18:02:48 发布

转载最新推荐文章于 2024-05-17 18:02:48 发布 · 539 阅读

0 ·

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/My-Dream/p/8804966.html

文章标签：

#数据库 #java

表	表名字	字段序号	字段名	字段类型	字段长度	小数位	主键	标识列	允许空	默认值	字段描述

SELECT 
	CASE sc.column_id WHEN 1 THEN st.name ELSE '' END AS '表'
	,CASE sc.column_id 
		WHEN 1 THEN 
				(SELECT TOP 1 VALUE 
				 FROM sys.extended_properties 
				 WHERE class =1 AND major_id=st.object_id AND minor_id=0 AND [name]='MS_Description'
				 ) 
	  ELSE '' END AS '表名字'
	,sc.column_id '字段序号',sc.name '字段名',sts.name '字段类型',sc.max_length '字段长度',sc.scale '小数位'
	,(CASE si.is_primary_key WHEN 1 THEN '√' ELSE '' END) '主键'
	,(CASE sc.is_identity WHEN 1 THEN '√' ELSE '' END) '标识列'
	--,(CASE sc.collation_name WHEN 'Chinese_PRC_CI_AS' THEN sc.max_length /2 ELSE sc.max_length END) AS '字段长度'
	,(case when sc.is_nullable=1 then '√' ELSE '' end) '允许空'
	,ISNULL(ssc.text,'') '默认值'
	,ISNULL(sep.value,'') '字段描述'
FROM sys.tables st
LEFT JOIN sys.columns sc ON st.object_id = sc.object_id
LEFT JOIN sys.extended_properties sep ON sep.class =1 AND sep.major_id=st.object_id AND sep.minor_id=sc.column_id 
LEFT JOIN SYS.types sts ON sc.system_type_id = sts.system_type_id AND sc.user_type_id = sts.user_type_id  
--默认值
LEFT JOIN sys.syscomments ssc ON sc.default_object_id = ssc.id
--主键
LEFT JOIN sys.sysindexkeys ssik ON ssik.id=st.object_id AND sc.column_id = ssik.colid 
LEFT JOIN sys.indexes si ON si.object_id=st.object_id AND si.index_id = ssik.indid --AND si.is_primary_key=1 
WHERE st.[type]='U' AND st.[name] IN('Fund_Account','Fund_TraceLog')
order by st.name,sc.column_id

转载于:https://www.cnblogs.com/My-Dream/p/8804966.html