SQL Server查詢資料庫所有預存程序、觸發器、索引資訊SQL分享

　　1. 查詢所有預存程序

 1 select Pr_Name as [預存程序], [參數]=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 parameter on 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 Parameter11 from sys.procedures Pr left join12 sys.parameters parameter on Pr.object_id = parameter.object_id13 inner join sys.types Type on parameter.system_type_id = Type.system_type_id14 where type = 'P'15 )tb16 where Pr_Name not like 'sp_%' --and Pr_Name not like 'dt%'17 group by Pr_Name18 order by Pr_Name

　　2. 預存程序資訊查詢

1 select Pr.Name as Pr_Name,parameter.name,T.Name,convert(varchar(32),parameter.max_length) as 參數長度,parameter.is_output as 是否是輸出參數,parameter.*2 from sys.procedures Pr left join3 sys.parameters parameter on Pr.object_id = parameter.object_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. 查詢所有觸發器

 1 select triggers.name as [觸發器],tables.name as [表名],triggers.is_disabled as [是否禁用], 2 triggers.is_instead_of_trigger AS [觸發器類型], 3 case when triggers.is_instead_of_trigger = 1 then 'INSTEAD OF' 4 when triggers.is_instead_of_trigger = 0 then 'AFTER' 5 else null 6 end as [觸發器類型描述] 7 from sys.triggers 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. 查詢所有索引

 1 select indexs.Tab_Name as [表名],indexs.Index_Name as [索引名] ,indexs.[Co_Names] as [索引列], 2 Ind_Attribute.is_primary_key as [是否主鍵],Ind_Attribute.is_unique AS [是否唯一鍵], 3 Ind_Attribute.is_disabled AS [是否禁用] 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 (1,2) 8 inner join sys.index_columns 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_id10 ) 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 ind13 inner join sys.tables tab on ind.Object_id = tab.object_id and ind.type in (1,2)14 inner join sys.index_columns index_columns on tab.object_id = index_columns.object_id and ind.index_id = index_columns.index_id15 inner join 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%'18 group by Tab_Name,Index_Name19 ) indexs inner join sys.indexes Ind_Attribute on indexs.Index_Name = Ind_Attribute.name20 order by indexs.Tab_Name

　　5. 顯示預存程序內容

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