How does SQL Server query all stored procedures, triggers, and index information in a database
This article mainly introduces SQL Server how to query all stored procedures, triggers and index information in the database. It is very detailed and has a certain reference value. Interested friends must read it!
1. Query all stored procedures
Select Pr_Name as [stored procedure], [parameter] = 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
two。 Stored procedure information query
Select Pr.Name as Pr_Name,parameter.name,T.Name,convert (varchar (32), parameter.max_length) as parameter length, whether parameter.is_output as is the output parameter, 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.nameplate parameters
3. Query all triggers
Select triggers.name as [trigger], tables.name as [Table name], triggers.is_disabled as [disable]
Triggers.is_instead_of_trigger AS [trigger type]
Case when triggers.is_instead_of_trigger = 1 then 'INSTEAD OF'
When triggers.is_instead_of_trigger = 0 then 'AFTER'
Else null
End as [trigger type description]
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. Query all indexes
Select indexs.Tab_Name as [table name], indexs.Index_Name as [index name], indexs [Co _ Names] as [index column]
Ind_Attribute.is_primary_key as [primary key], Ind_Attribute.is_unique AS [unique key]
Ind_Attribute.is_disabled AS [whether to disable]
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 (1mai 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 (1mai 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. Display the contents of the stored procedure
SELECT TEXT FROM syscomments WHERE id=object_id ('SP_NAME') SP_HELPTEXT' SP_NAME' is all the contents of the article "how to query all stored procedures, triggers, and index information in a database by SQL Server". Thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!
