SQL Server queries all stored procedure information, triggers, indexes

1. [Code]Querying all stored Procedures skip to [1] [2] [3] [4] [5] [6] [Full screen preview]?

123456789101112131415161718

select Pr_Name as [存储过程], [参数]=stuff((select ‘，‘+[Parameter]from (select Pr.Name as Pr_Name,parameter.name +‘ ‘ +Type.Name + ‘ (‘+convert(varchar(32),parameter.max_length)+‘)‘ as Parameterfrom sys.procedures Pr left joinsys.parameters parameter on Pr.object_id = parameter.object_idinner join sys.types Type on parameter.system_type_id = Type.system_type_idwhere 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 Parameterfrom sys.procedures Pr left joinsys.parameters parameter on Pr.object_id = parameter.object_idinner join sys.types Type on parameter.system_type_id = Type.system_type_idwhere type = ‘P‘)tbwhere Pr_Name not like ‘sp_%‘ --and Pr_Name not like ‘dt%‘group by Pr_Nameorder by Pr_Name

2. [Code]Querying all triggers skip to [1] [2] [3] [4] [5] [6] [Full screen preview]?

12345678910

select triggers.name as [触发器],tables.name as [表名],triggers.is_disabled as [是否禁用],triggers.is_instead_of_trigger AS [触发器类型],case when triggers.is_instead_of_trigger = 1 then ‘INSTEAD OF‘when triggers.is_instead_of_trigger = 0 then ‘AFTER‘else nullend as [触发器类型描述]from sys.triggers triggersinner join sys.tables tables on triggers.parent_id = tables.object_idwhere triggers.type =‘TR‘order by triggers.create_date

3. [Code]Querying all Indexes skip to [1] [2] [3] [4] [5] [6] [Full screen preview]?

1234567891011121314151617181920

select indexs.Tab_Name as [表名],indexs.Index_Name as [索引名] ,indexs.[Co_Names] as [索引列],Ind_Attribute.is_primary_key as [是否主键],Ind_Attribute.is_unique AS [是否唯一键],Ind_Attribute.is_disabled AS [是否禁用]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 indinner join sys.tables tab on ind.Object_id = tab.object_id and ind.type in (1,2)inner join sys.index_columns index_columns on tab.object_id = index_columns.object_id and ind.index_id = index_columns.index_idinner 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 indinner join sys.tables tab on ind.Object_id = tab.object_id and ind.type in (1,2)inner join sys.index_columns index_columns on tab.object_id = index_columns.object_id and ind.index_id = index_columns.index_idinner join sys.columns Col on tab.object_id = Col.object_id and index_columns.column_id = Col.column_id)tbwhere Tab_Name not like ‘sys%‘group by Tab_Name,Index_Name) indexs inner join sys.indexes Ind_Attribute on indexs.Index_Name = Ind_Attribute.nameorder by indexs.Tab_Name

4. [Code][SQL] Code skip to [1] [2] [3] [4] [5] [6] [Full screen preview]?

12345678910111213141516171819202122232425

DECLARE @s VARCHAR(4000),@n INT,@i INT,@s1 VARCHAR(100)SELECT IDENTITY(INT) id,text INTO ##FROM syscommentsSELECT @[email protected]@ROWCOUNT,@i=0WHILE @i<@nBEGINSELECT @[email protected]+1,@s=‘‘SELECT @s1=REPLACE(REPLACE(RTRIM(LTRIM(STUFF(STUFF(text,CHARINDEX(‘AS‘,text),40000,‘‘),1,CHARINDEX(‘PROC‘,STUFF(text,CHARINDEX(‘AS‘,text),40000,‘‘))+4,‘‘))),CHAR(10),‘‘),CHAR(13),‘‘)FROM ## WHERE ID=RTRIM(@i)--SELECT @s1,ASCII(SUBSTRING(@s1,3,1))--SELECT LEN(REPLACE(REPLACE(@s1,CHAR(13),‘‘),CHAR(10),‘‘))SELECT @s=‘SELECT text FROM tempdb.dbo.## WHERE ID=‘ + RTRIM(@i)EXEC(‘EXEC master..xp_cmdshell ‘‘bcp "‘ + @s + ‘ " queryout "e:\ProcTXT\‘ + @s1 + ‘.txt" -S"ROBINHOME\SQLEXPRESS" -c -U"sa" -P"bd5178"‘‘‘)ENDDROP TABLE ##--自己写的 如何识别换行？？？ 还有些非存储过程的对象SELECT top 10 text FROM syscomments where id in(select object_id from sys.procedures where type = ‘P‘)SELECT text FROM syscomments where id in(select object_id from sys.procedures where type = ‘P‘)and charindex(‘ALLSTOCK‘,text)>0and charindex(‘CREATE PROCEDURE‘,text)>0

5. [Code]Show stored Procedure contents skip to [1] [2] [3] [4] [5] [6] [Full screen preview]?

123	SELECTTEXT FROM syscomments WHERE id=object_id(‘SP_NAME‘)SP_HELPTEXT ‘SP_NAME‘

6. [Code]Get only user-defined stored procedures skip to [1] [2] [3] [4] [5] [6] [Full screen preview]?

123456

USE [your_database_name_here];GOSELECT * FROM sys.all_objectsWHERE ([type] = ‘P‘ OR [type] = ‘X‘ OR [type] = ‘PC‘) AND [is_ms_shipped] = 0 ORDER BY [name];GOSELECT * FROM sysobjects where type=‘P‘

SQL Server queries all stored procedure information, triggers, indexes