SQL Server如何查询数据库所有存储过程、触发器、索引信息

共计 2967 个字符，预计需要花费 8 分钟才能阅读完成。

这篇文章主要介绍 SQL Server 如何查询数据库所有存储过程、触发器、索引信息，文中介绍的非常详细，具有一定的参考价值，感兴趣的小伙伴们一定要看完！

1.  查询所有存储过程
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

2.  存储过程信息查询

select Pr.Name as Pr_Name,parameter.name,T.Name,convert(varchar(32),parameter.max_length) as  参数长度,parameter.is_output as  是否是输出参数,parameter.*

 from sys.procedures Pr left join

 sys.parameters parameter on Pr.object_id = parameter.object_id

 inner join sys.types T on parameter.system_type_id = T.system_type_id 5 where Pr.type =  P  and Pr.Name like  order_%  and T.name!= sysname  order by Pr.Name

3.  查询所有触发器
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

4.  查询所有索引
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)
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)
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

5.  显示存储过程内容

SELECT TEXT FROM syscomments WHERE id=object_id(SP_NAME)
SP_HELPTEXT  SP_NAME

以上是“SQL Server 如何查询数据库所有存储过程、触发器、索引信息”这篇文章的所有内容，感谢各位的阅读！希望分享的内容对大家有帮助，更多相关知识，欢迎关注丸趣 TV 行业资讯频道！