SQL Server queries all stored procedures, triggers, and indexes of a Database. SQL sharing

1.Query all stored procedures

 1   Select Pr_name As   [  Stored Procedure  ] , [  Parameters  ]  =  Stuff (( Select   '  ,  '  + [  Parameter  ]  2   From  (  3   Select Pr. Name As Pr_name, parameter. Name +  '   '   + Type. Name +   '  (  ' +  Convert ( Varchar ( 32 ), Parameter. max_length) +  '  )  '   As  Parameter  4   From SYS. Procedures PR Left   Join  5 SYS. Parameters ParameterOn Pr. Object_id   = Parameter. Object_id  6   Inner   Join SYS. types type On Parameter. system_type_id =  Type. system_type_id  7   Where Type =   '  P '  8 ) T Where Pr_name = TB. pr_name For XML Path ( '' )), 1 , 1 , ''  )  9   From  (  10   Select Pr. Name As Pr_name, parameter. Name +  '   '   + Type. Name +   '  (  '  +  Convert ( Varchar ( 32 ), Parameter. max_length) +  ' )  '   As  Parameter  11   From SYS. Procedures PR Left   Join  12 SYS. Parameters Parameter On Pr. Object_id   = Parameter. Object_id  13   Inner   Join SYS. types type On Parameter. system_type_id =  Type. system_type_id  14   Where Type =   '  P  '  15   ) TB  16   Where Pr_name Not   Like   ' SP _ %  '   --  And pr_name not like 'dt %'  17   Group   By  Pr_name  18   Order   By Pr_name

2.Query stored procedure information

 1   Select Pr. Name As Pr_name, parameter. Name, T. Name, Convert ( Varchar ( 32 ), Parameter. max_length) As Parameter length, parameter. is_output As Whether it is an output parameter, parameter. *  2   From SYS. Procedures PR Left   Join  3 SYS. Parameters Parameter On Pr. Object_id   = Parameter.Object_id  4   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.Query all triggers

 1   Select Triggers. Name As   [  Trigger  ] , Tables. NameAs   [  Table Name  ] , Triggers. is_disabled As   [  Disable?  ]  ,  2 Triggers. is_instead_of_trigger As   [  Trigger type  ]  ,  3   Case  When Triggers. is_instead_of_trigger =   1   Then   '  Instead  '  4   When Triggers. is_instead_of_trigger =   0   Then   '  After  '  5  Else   Null  6   End   As   [  Trigger type description  ]  7   From  SYS. triggers  8   Inner   Join SYS. Tables tables On Triggers. parent_id = Tables.Object_id  9   Where Triggers. Type =  '  Tr  '  10   Order   By Triggers. create_date

4.Query all indexes

 1   Select Indexs. tab_name As   [  Table Name ] , Indexs. index_name As   [  Index name  ] , Indexs. [  Co_names  ]   As   [  Index Column  ]  ,  2 Ind_attribute.is_primary_key As   [ Primary Key?  ] , Ind_attribute.is_unique As   [  Unique or not  ]  ,  3 Ind_attribute.is_disabled As   [  Disable?  ]  4   From  (  5   Select Tab_name, index_name, [  Co_names  ]  =  Stuff (( Select   '  ,  '  +  [  Co_name  ]   From  6 ( Select Tab. NameAs 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 (1 , 2  )  8   Inner   Join SYS. index_columns On Tab. Object_id   = Index_columns. 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_id  10 ) T Where Tab_name = TB. tab_name And Index_name = TB. index_name For XML Path ( '' )), 1 , 1 , ''  )  11   From  (  12   Select Tab. Name As Tab_name, Ind. Name As Index_name, col. Name As Co_name From  SYS. Indexes ind  13   Inner   Join SYS. Tables Tab On Ind. Object_id   = Tab. Object_id   And Ind. Type In ( 1 , 2  ) 14   Inner   Join SYS. index_columns On Tab. Object_id   = Index_columns. Object_id   And Ind. index_id =  Index_columns.index_id  15   Inner   Join SYS. Columns col On Tab.Object_id   = Col. Object_id   And Index_columns.column_id =  Col. column_id  16   ) TB  17   Where Tab_name Not   Like   '  Sys %  '  18  Group   By  Tab_name, index_name  19 ) Indexs Inner   Join SYS. Indexes ind_attribute On Indexs. index_name =  Ind_attribute.name  20   Order   By Indexs. tab_name

5.Display Stored Procedure content

Select Text FromSyscommentsWhereID=Object_id('Sp_name') Sp_helptext'Sp_name'