Shulou(Shulou.com)05/31 Report--

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!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.