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

时间:2024-08-19 17:03:56

1. [代码]查询所有存储过程     

01 select Pr_Name as [存储过程], [参数]=stuff((select ','+[Parameter]
02 from (
03 select Pr.Name as Pr_Name,parameter.name +' ' +Type.Name ' ('+convert(varchar(32),parameter.max_length)+')' as Parameter
04 from sys.procedures Pr left join
05 sys.parameters parameter on Pr.object_id = parameter.object_id
06 inner join sys.types Type on parameter.system_type_id = Type.system_type_id
07 where type = 'P'
08 ) t where Pr_Name=tb.Pr_Name for xml path('')), 1, 1, '')
09 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. [代码]查询所有触发器     

01 select triggers.name as [触发器],tables.name as [表名],triggers.is_disabled as [是否禁用],
02 triggers.is_instead_of_trigger AS [触发器类型],
03 case when triggers.is_instead_of_trigger = 1 then 'INSTEAD OF'
04 when triggers.is_instead_of_trigger = 0 then 'AFTER'
05 else null
06 end as [触发器类型描述]
07 from sys.triggers triggers
08 inner join sys.tables tables on triggers.parent_id = tables.object_id
09 where triggers.type ='TR'
10 order by triggers.create_date

3. [代码]查询所有索引     

01 select indexs.Tab_Name as [表名],indexs.Index_Name as [索引名] ,indexs.[Co_Names] as[索引列],
02 Ind_Attribute.is_primary_key as [是否主键],Ind_Attribute.is_unique AS [是否唯一键],
03 Ind_Attribute.is_disabled AS [是否禁用]
04 from (
05 select Tab_Name,Index_Name, [Co_Names]=stuff((select ','+[Co_Name] from
06 select tab.Name as Tab_Name,ind.Name as Index_Name,Col.Name as Co_Name fromsys.indexes ind
07 inner join sys.tables tab on ind.Object_id = tab.object_id and ind.type in (1,2)
08 inner join sys.index_columns index_columns on tab.object_id = index_columns.object_idand ind.index_id = index_columns.index_id
09 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 index_columns on tab.object_id = index_columns.object_idand 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

4. [代码][SQL]代码     

01 DECLARE @s VARCHAR(4000),@n INT,@i INT,@s1 VARCHAR(100)
02 SELECT IDENTITY(INT) id,text INTO ##
03 FROM syscomments
04 SELECT @n=@@ROWCOUNT,@i=0
05 WHILE @i<@n
06 BEGIN
07 SELECT @i=@i+1,@s=''
08 SELECT@s1=REPLACE(REPLACE(RTRIM(LTRIM(STUFF(STUFF(text,CHARINDEX('AS',text),40000,''),1,
09 CHARINDEX('PROC',STUFF(text,CHARINDEX('AS',text),40000,''))+4,''))),CHAR(10),''),CHAR(13),'')
10 FROM ## WHERE ID=RTRIM(@i)
11 --SELECT @s1,ASCII(SUBSTRING(@s1,3,1))
12 --SELECT LEN(REPLACE(REPLACE(@s1,CHAR(13),''),CHAR(10),''))
13 SELECT @s='SELECT text FROM tempdb.dbo.## WHERE ID=' + RTRIM(@i)
14 EXEC('EXEC master..xp_cmdshell ''bcp "' + @s + ' " queryout "e:\ProcTXT\' + @s1 + '.txt" -S"ROBINHOME\SQLEXPRESS" -c -U"sa" -P"bd5178"''')
15 END
16 DROP TABLE ##
17  
18 --自己写的 如何识别换行??? 还有些非存储过程的对象
19 SELECT top 10 text FROM syscomments where id in(
20 select object_id from sys.procedures where type = 'P')
21  
22 SELECT text FROM syscomments where id in(
23 select object_id from sys.procedures where type = 'P')
24 and charindex('ALLSTOCK',text)>0
25 and charindex('CREATE PROCEDURE',text)>0

5. [代码]显示存储过程内容     

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

6. [代码]获取只有用户定义的存储过程     

1 USE [your_database_name_here];
2 GO
3 SELECT FROM sys.all_objects
4 WHERE ([type] = 'P' OR [type] = 'X' OR [type] = 'PC'AND [is_ms_shipped] = 0 ORDER BY[name];
5 GO
6 SELECT FROM sysobjects where type='P'