数据库常用对象查询 整理

[天-笑]

--查询用户表对象信息
select  Tab.Name as [表名],Tab.create_date as [创建时间],Tab.modify_date  as [最后修改时间],
		Col.Name  as [列名] ,Type.name as [数据类型],Col.max_length as [字段长度],
		CASE WHEN pk.is_primary_key= 1 THEN 'Y' ELSE 'N' end as [是否主键],
		CASE WHEN Col.is_identity = 1 THEN 'Y' else 'N'end as [是否自增] ,
		identity_columns.seed_value as [自增种子],identity_columns.increment_value as [自增步长],
		case when Col.is_nullable = 1 then 'Y' else 'N' END AS [是否允许为NULL],
		Def.text as [默认值],case when Col.is_computed = 1 then 'Y' else 'N' END as [是否计算列] ,
		computed_columns.definition as [计算公式],Col_Desc.Value as [列备注]
from sys.objects Tab inner join sys.columns Col on Tab.object_id =Col.object_id
inner join sys.types Type on Col.system_type_id = Type.system_type_id
left join sys.identity_columns identity_columns on  Tab.object_id = identity_columns.object_id and Col.column_id = identity_columns.column_id
left join syscomments Def  on Col.default_object_id = Def.ID
left join(
	select index_columns.object_id,index_columns.column_id,indexes.is_primary_key 
	from sys.indexes  indexes inner join sys.index_columns index_columns 
	on indexes.object_id = index_columns.object_id and indexes.index_id = index_columns.index_id
	where indexes.is_primary_key = 1/*主键*/
) PK on Tab.object_id = PK.object_id AND Col.column_id = PK.column_id
left join sys.computed_columns  computed_columns on Tab.object_id =computed_columns.object_id and Col.column_id = computed_columns.column_id
left join sys.extended_properties Col_Desc on Col_Desc.major_id=Tab.object_id and Col_Desc.minor_id=Col.Column_id and Col_Desc.class=1 
where Tab.type = 'U' and Tab.Name not like'sys%'
order by Tab.create_date



--查询所有视图
select views.Name as [视图名],Col.Name as [列名] ,Type.name as [数据类型],Col.max_length as [字段长度]
		--,Col_Desc.Value as Col_Description
from sys.views views
inner join sys.columns Col on views.object_id  = Col.object_id
inner join sys.types Type on Col.system_type_id = Type.system_type_id
--left join sys.extended_properties Col_Desc 
--	on Col_Desc.major_id=views.object_id and Col_Desc.minor_id=Col.Column_id and Col_Desc.class=1 
order by Create_Date


--查询外键约束
select FK_Name as [外键名],Parent_Tab_Name as [外键表],
	[外键列]=stuff((select '，'+[Parent_Col_Name] from (
		select	FK.name as FK_Name,Parent_Tab.Name as Parent_Tab_Name,Parent_Col.Name as Parent_Col_Name,
				Referenced_Tab.Name as Referenced_Tab_Name,Referenced_Col.Name as Referenced_Col_Name
		from sys.foreign_keys FK
		inner join sys.foreign_key_columns Col on FK.Object_ID = Col.constraint_object_id
		inner join sys.objects Parent_Tab ON  Col.parent_object_id = Parent_Tab.Object_ID and Parent_Tab.TYPE = 'U'
		inner join sys.columns Parent_Col on Parent_Tab.Object_ID = Parent_Col.object_id 
											and  Col.parent_column_id = Parent_Col.column_id
		inner join sys.objects Referenced_Tab ON  Col.referenced_object_id = Referenced_Tab.Object_ID and Referenced_Tab.TYPE = 'U'
		inner join sys.columns Referenced_Col on Referenced_Tab.Object_ID = Referenced_Col.object_id 
											and  Col.referenced_column_id = Referenced_Col.column_id
	)t where FK_Name=tb.FK_Name  and Parent_Tab_Name = tb.Parent_Tab_Name and Referenced_Tab_Name = tb.Referenced_Tab_Name   for xml path('')), 1, 1, ''),
	Referenced_Tab_Name as  [主键表],
	[主键列]=stuff((select '，'+[Referenced_Col_Name] from (
		select	FK.name as FK_Name,Parent_Tab.Name as Parent_Tab_Name,Parent_Col.Name as Parent_Col_Name,
				Referenced_Tab.Name as Referenced_Tab_Name,Referenced_Col.Name as Referenced_Col_Name
		from sys.foreign_keys FK
		inner join sys.foreign_key_columns Col on FK.Object_ID = Col.constraint_object_id
		inner join sys.objects Parent_Tab ON  Col.parent_object_id = Parent_Tab.Object_ID and Parent_Tab.TYPE = 'U'
		inner join sys.columns Parent_Col on Parent_Tab.Object_ID = Parent_Col.object_id 
											and  Col.parent_column_id = Parent_Col.column_id
		inner join sys.objects Referenced_Tab ON  Col.referenced_object_id = Referenced_Tab.Object_ID and Referenced_Tab.TYPE = 'U'
		inner join sys.columns Referenced_Col on Referenced_Tab.Object_ID = Referenced_Col.object_id 
											and  Col.referenced_column_id = Referenced_Col.column_id
	)t where FK_Name=tb.FK_Name  and Parent_Tab_Name = tb.Parent_Tab_Name and Referenced_Tab_Name = tb.Referenced_Tab_Name   for xml path('')), 1, 1, '')
	--as [外键列]
from (
	select	FK.name as FK_Name,Parent_Tab.Name as Parent_Tab_Name,Parent_Col.Name as Parent_Col_Name,
			Referenced_Tab.Name as Referenced_Tab_Name,Referenced_Col.Name as Referenced_Col_Name
	from sys.foreign_keys FK
	inner join sys.foreign_key_columns Col on FK.Object_ID = Col.constraint_object_id
	inner join sys.objects Parent_Tab ON  Col.parent_object_id = Parent_Tab.Object_ID and Parent_Tab.TYPE = 'U'
	inner join sys.columns Parent_Col on Parent_Tab.Object_ID = Parent_Col.object_id 
										and  Col.parent_column_id = Parent_Col.column_id
	inner join sys.objects Referenced_Tab ON  Col.referenced_object_id = Referenced_Tab.Object_ID and Referenced_Tab.TYPE = 'U'
	inner join sys.columns Referenced_Col on Referenced_Tab.Object_ID = Referenced_Col.object_id 
										and  Col.referenced_column_id = Referenced_Col.column_id
)tb
group by FK_Name,Parent_Tab_Name,Referenced_Tab_Name


--查询所有存储过程
select Pr_Name  as [存储过程], [参数]=stuff((select '，'+[Parameter] 
	from (
		select Pr.Name as Pr_Name,parameter.name +' ' +Type.Name + ' ('+convert(varchar(32),parameter.max_length)+')' as Parameter
		from sys.procedures Pr left join 
		sys.parameters parameter  on Pr.object_id = parameter.object_id
		inner join sys.types Type on parameter.system_type_id = Type.system_type_id
		where type = 'P' 
	) t where Pr_Name=tb.Pr_Name for xml path('')), 1, 1, '')
from (
	select Pr.Name as Pr_Name,parameter.name +' ' +Type.Name + ' ('+convert(varchar(32),parameter.max_length)+')' as Parameter
	from sys.procedures Pr left join 
	sys.parameters parameter  on Pr.object_id = parameter.object_id
	inner join sys.types Type on parameter.system_type_id = Type.system_type_id
	where type = 'P' 
)tb
where Pr_Name not like 'sp_%' --and Pr_Name not like 'dt%'
group by Pr_Name
order by Pr_Name

--查询所有触发器
select triggers.name as [触发器],tables.name as [表名],triggers.is_disabled as [是否禁用],
triggers.is_instead_of_trigger AS [触发器类型],
case when triggers.is_instead_of_trigger = 1 then 'INSTEAD OF'
	 when triggers.is_instead_of_trigger = 0 then 'AFTER'
	 else null
end as [触发器类型描述]
from sys.triggers triggers
inner join sys.tables tables on triggers.parent_id = tables.object_id
where triggers.type ='TR'
order by triggers.create_date

--查询所有索引
select	indexs.Tab_Name  as [表名],indexs.Index_Name as [索引名] ,indexs.[Co_Names] as [索引列],
		Ind_Attribute.is_primary_key as [是否主键],Ind_Attribute.is_unique AS [是否唯一键],
		Ind_Attribute.is_disabled AS [是否禁用]
 from (
	select Tab_Name,Index_Name, [Co_Names]=stuff((select '，'+[Co_Name] from 
	(	select tab.Name as Tab_Name,ind.Name as Index_Name,Col.Name as Co_Name from sys.indexes ind 
		inner join sys.tables tab on ind.Object_id = tab.object_id and ind.type in (1,2)/*索引的类型：0=堆/1=聚集/2=非聚集/3=XML*/
		inner join sys.index_columns index_columns on tab.object_id = index_columns.object_id and ind.index_id = index_columns.index_id
		inner join sys.columns Col on tab.object_id = Col.object_id and index_columns.column_id = Col.column_id
	) t where Tab_Name=tb.Tab_Name and Index_Name=tb.Index_Name  for xml path('')), 1, 1, '')
	from (
		select tab.Name as Tab_Name,ind.Name as Index_Name,Col.Name as Co_Name from sys.indexes ind 
		inner join sys.tables tab on ind.Object_id = tab.object_id and ind.type in (1,2)/*索引的类型：0=堆/1=聚集/2=非聚集/3=XML*/
		inner join sys.index_columns index_columns on tab.object_id = index_columns.object_id and ind.index_id = index_columns.index_id
		inner join sys.columns Col on tab.object_id = Col.object_id and index_columns.column_id = Col.column_id
	)tb
	where Tab_Name not like 'sys%'
	group by Tab_Name,Index_Name
) indexs inner join sys.indexes  Ind_Attribute on indexs.Index_Name = Ind_Attribute.name
order by indexs.Tab_Name

[hefuming5]

收藏了

[天-笑]

到300楼 我就结帖了

[youqi1984]

关注。总结的不错

[nanerfeng2002]

不错的资料，好好研究一下，谢谢

[snow80mail]

来学习了, 谢谢楼主的分享.

[jm_qinqin]

学习啦！！！

[jacky199711]

好啊好啊好啊 好啊

[Roach2000]

不错的东西

[ZHULIULIU]

收藏备用

准备学习

[天-笑]

还能到300楼啊？

[迷茫的主宰]

楼主辛苦了！

[lhy_soft]

多谢楼主

[lhsoft]

mark~~~~~

[hanchunyang123]

收藏收藏赶紧收藏

[sanshn]

刚刚在SQL 2000 测试,都不通过...为什么?

服务器: 消息 208，级别 16，状态 1，行 2
对象名 'sys.objects' 无效。
服务器: 消息 208，级别 16，状态 1，行 2
对象名 'sys.columns' 无效。
服务器: 消息 208，级别 16，状态 1，行 2
对象名 'sys.types' 无效。
服务器: 消息 208，级别 16，状态 1，行 2
对象名 'sys.identity_columns' 无效。
服务器: 消息 208，级别 16，状态 1，行 2
对象名 'sys.indexes' 无效。
服务器: 消息 208，级别 16，状态 1，行 2
对象名 'sys.index_columns' 无效。
服务器: 消息 208，级别 16，状态 1，行 2
对象名 'sys.computed_columns' 无效。
服务器: 消息 208，级别 16，状态 1，行 2
对象名 'sys.extended_properties' 无效。

[beg200710]

呵呵 不错

[天-笑]

[Quote=引用 294 楼 sanshn 的回复:]

刚刚在SQL 2000 测试,都不通过...为什么?

服务器: 消息 208，级别 16，状态 1，行 2
对象名 'sys.objects' 无效。
服务器: 消息 208，级别 16，状态 1，行 2
对象名 'sys.columns' 无效。
服务器: 消息 208，级别 16，状态 1，行 2
对象名 'sys.types' 无效。
服务器: 消息 208，级别 16，……
[/Quote]

该语句支持 SQL2005 及以上版本，SQO2000里没有 我用到的相关系统视图

[光行天下]

学习学习

[ChinaITOldMan]

very good

[fanfanxg]

太棒了，谢谢分享