Sql Server 遍历数据库文件找出使用了某个表/存储过程的所有存储过程-CSDN博客

Sql Server 遍历数据库文件找出使用了某个表/存储过程的所有存储过程。

 1 CREATE Procedure SP_GetProc      
 2 @Object_Name Varchar(50)      
 3 as      
 4 Set nocount on      
 5   
 6 DECLARE @tmptable TABLE    
 7 (    
 8 ID int IDENTITY (1, 1),    
 9 [DataBase] Varchar(100),  
10 Name Varchar(500),  
11 Type Varchar(100),  
12 Create_Date Datetime  
13 )    
14   
15 DECLARE @SQLText TABLE    
16 (    
17 ID int IDENTITY (1, 1),    
18 SQLText Varchar(Max)  
19 )    
20   
21 if Charindex('#', @Object_Name) > 0  
22 begin   
23   Select @Object_Name = replace(@Object_Name, '#', '')  
24   
25   Insert Into @SQLText  
26     Select '      
27     Select ''' + Name + ''' [DataBase], CONVERT(VARCHAR(500), a.Name COLLATE Chinese_PRC_CI_AS) Name, a.Type, a.Create_Date      
28       From ' + Name+ '.sys.all_objects a   
29       Where a.Name Like ''%' + @Object_Name + '%'''      
30       From sys.databases where database_ID > 4      
31 end  
32 else begin  
33   Insert Into @SQLText  
34     Select '      
35     Select ''' + Name + ''' [DataBase], CONVERT(VARCHAR(500), a.Name COLLATE Chinese_PRC_CI_AS) Name, a.Type, a.Create_Date      
36       From ' + Name+ '.sys.all_objects a, ' + Name + '.sys.syscomments b       
37       Where a.object_id = b.id and b.text Like ''%' + @Object_Name + '%'''      
38       From sys.databases where database_ID > 4      
39 end  
40   
41 Declare @ID Int, @MID Int, @SQl Varchar(Max)  
42 Select @ID = 1, @MID = MAX(ID) From @SQLText  
43 While @ID <= @MID  
44 begin   
45   Select @SQl = SQLText From @SQLText Where ID = @ID  
46   Insert Into @tmptable Exec (@SQl)   
47   Select @ID = @ID + 1  
48 end  
49   
50 Select * From @tmptable Order by ID