SQL Server查询所有存储过程信息、触发器、索引

 

1. [代码]查询所有存储过程     跳至 [1] [2] [3] [4] [5] [6] [全屏预览]

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18

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 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

2. [代码]查询所有触发器     跳至 [1] [2] [3] [4] [5] [6] [全屏预览]

?

1 2 3 4 5 6 7 8 9 10

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 null end as [触发器类型描述] 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

3. [代码]查询所有索引     跳至 [1] [2] [3] [4] [5] [6] [全屏预览]

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20

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 ind inner 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_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 (1,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

4. [代码][SQL]代码     跳至 [1] [2] [3] [4] [5] [6] [全屏预览]

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25

DECLARE @s VARCHAR (4000),@n INT ,@i INT ,@s1 VARCHAR (100) SELECT IDENTITY( INT ) id,text INTO ## FROM syscomments SELECT @n=@@ROWCOUNT,@i=0 WHILE @i<@n BEGIN SELECT @i=@i+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" '' ') END DROP 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)>0 and charindex(' CREATE PROCEDURE ',text)>0

5. [代码]显示存储过程内容     跳至 [1] [2] [3] [4] [5] [6] [全屏预览]

?

1 2 3

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

6. [代码]获取只有用户定义的存储过程     跳至 [1] [2] [3] [4] [5] [6] [全屏预览]

?

1 2 3 4 5 6

USE [your_database_name_here]; GO SELECT * FROM sys.all_objects WHERE ([type] = 'P' OR [type] = 'X' OR [type] = 'PC' ) AND [is_ms_shipped] = 0 ORDER BY [ name ]; GO SELECT * FROM sysobjects where type= 'P'

转载于:https://www.cnblogs.com/lvdongjie/p/5555204.html