SQL Server query database all stored procedures, triggers, index information SQL share

1. querying all stored procedures

1 Select Pr_name as [Stored Procedure], [Parameter]=stuff ((select ', ' +[parameter] 2 from (3 select Pr.name as Pr_name,parameter.name + ' +t Ype.  Name + ' (' +convert (varchar (+), parameter.max_length) + ') ' as parameter 4 from Sys.procedures Pr left join 5 sys.parameters Parameter on pr.object_id = parameter.object_id 6 inner join sys.types Type on parameter.system_type_id = Type.system_typ e_id 7 WHERE type = ' P ' 8) t where PR_NAME=TB. Pr_name FOR XML Path (")), 1, 1, ') 9 from (Ten select Pr.name as Pr_name,parameter.name + ' +type.name + ' (' +convert (VA Rchar (+), parameter.max_length) + ' as Parameter11 from Sys.procedures Pr left join12 sys.parameters parameter on Pr.obje ct_id = parameter.object_id13 INNER join sys.types type on parameter.system_type_id = TYPE.SYSTEM_TYPE_ID14 where Type = ' P ' () Tb16 where pr_name not like ' sp_% '--and pr_name don't like ' dt% ' ~ GROUP by Pr_name18 ORDER by Pr_name

2. stored procedure information query

1 Select Pr.name as pr_name,parameter.name,t.name,convert (varchar (), parameter.max_length) as parameter length, Parameter.is_ Output as is an out parameter, parameter.*2 from Sys.procedures Pr left join3 sys.parameters parameter on pr.object_id = Parameter.obje Ct_id4 INNER JOIN sys.types T on parameter.system_type_id = T.SYSTEM_TYPE_ID5 where pr.type = ' P ' and pr.name like ' Order_ % ' and t.name!= ' sysname ' ORDER by pr.name

3. query all triggers

1 Select Triggers.name as [Trigger],tables.name as [table name],triggers.is_disabled as [disabled], 2 triggers.is_instead_of_trigger as [touch Sender type], 3 case when Triggers.is_instead_of_trigger = 1 Then ' instead of ' 4 when Triggers.is_instead_of_trigger = 0 Then ' AF TER ' 5 else null 6 end as [Trigger type description] 7 from sys.triggers triggers 8 inner join sys.tables tables on triggers.parent_id = Ta bles.object_id 9 where Triggers.type = ' TR ' ORDER by triggers.create_date

4. Querying all Indexes

 1 Select Indexs. Tab_name as [table name],indexs. Index_name as [index name], Indexs. [Co_names] as [indexed column], 2 ind_attribute.is_primary_key as [whether the primary key],ind_attribute.is_unique as [is a unique key], 3 Ind_attribute.is_ Disabled as [is disabled] 4 from (5 select Tab_name,index_name, [Co_names]=stuff ((SELECT ', ' +[co_name ') from 6 (select Tab. Name as Tab_name,ind. Name as index_name,col.name as Co_name from sys.indexes IND 7 INNER JOIN Sys.tables tab on Ind. object_id = tab.object_id and Ind.type in (8) inner join sys.index_columns index_columns on tab.object_id = Index_colu mns.object_id and ind.index_id = index_columns.index_id 9 inner join sys.columns Col on tab.object_id = col.object_id and index_columns.column_id = col.column_id10) t where TAB_NAME=TB. Tab_name and INDEX_NAME=TB. Index_name FOR XML Path (")), 1, 1, ') One from (select tab. Name as Tab_name,ind. Name as index_name,col.name as Co_name from sys.indexes ind13 inner join Sys.tables tab on Ind. object_id = tab.object_id and Ind.type in (from) inner join Sys.indEx_columns index_columns on tab.object_id = index_columns.object_id and ind.index_id = index_columns.index_id15 inner Joi n sys.columns Col on tab.object_id = col.object_id and index_columns.column_id = col.column_id16) tb17 where Tab_Name not Like ' sys% ' (Group by TAB_NAME,INDEX_NAME19) Indexs Inner joins sys.indexes Ind_attribute on Indexs. Index_name = Ind_attribute.name20 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 '

SQL Server query database all stored procedures, triggers, index information SQL share