Sql Server 遍曆資料庫檔案找出使用了某個表/預存程序的所有預存程序

標籤：

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  

View Code

 

Sql Server 遍曆資料庫檔案找出使用了某個表/預存程序的所有預存程序