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
