查询字符串在数据库哪些表那些列存在/根据字符串快速定位表定位列

 1 SET QUOTED_IDENTIFIER OFF 
 2 GO
 3 SET ANSI_NULLS OFF 
 4 GO
 5 
 6 IF EXISTS(SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'sp_FindString') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
 7     DROP PROCEDURE sp_FindString
 8 GO
 9   
10  /* 
11     查询方式：exec sp_FindString '%张三%'
12     查询字符串在哪些表那些列存在
13     快速根据页面上显示的文字字符串定位到数据库的哪些关联表中
14   */
15 CREATE PROCEDURE [dbo].[sp_FindString]
16 (
17     @old_string varchar(50)
18 )    
19 as    
20 begin    
21     declare @tbname varchar(50)    
22     declare tbroy cursor for 
23         select name from sysobjects where xtype= 'U ' --第一个游标遍历所有的表    
24     open tbroy    
25     fetch next from tbroy into @tbname    
26     while @@fetch_status=0    
27     begin  
28         declare @colname varchar(500)    
29         declare colroy cursor for select name from syscolumns where id=object_id(@tbname) and xtype in    
30         (select xtype from systypes where name in ( 'varchar', 'nvarchar', 'char', 'nchar', 'text', 'int'  
31         , 'decimal', 'float', 'int', 'money')) --第二个游标是第一个游标的嵌套游标，遍历某个表的所有字段    
32         open colroy    
33         fetch next from colroy into @colname    
34         while @@fetch_status=0    
35         begin
36              if(@colname=@old_string)  
37             begin  
38               print '包含有列的表'+ @colname +'包含有列的表'+@tbname    
39             end  
40   
41             declare @sql nvarchar(1000),@j int    
42             select @sql= 'select @i=count(1) from ' + '[' + @tbname + ']' + ' where '+ '[' + @colname + ']'+ ' like '+ ''''+ @old_string + ''''
43              --select @sql    
44             exec dbo.sp_executesql @sql,N'@i int output', @i = @j output --输出满足条件表的记录数    
45             if @j> 0    
46             begin
47                 select 包含字串的表名 = @tbname ,包含字串的列名 = @colname    
48                select @sql = 'select ' + @colname + ',* from ' + @tbname + ' where '+ @colname + ' like '+ '''' + @old_string + ''''    
49               --select @sql = 'UPDATE '+ @tbname + ' set '+ @colname   + ' = REPLACE(  '+ @colname + ' ， ''任我行'',''LEMON'')'    
50               print @sql    
51               --exec(@sql)    
52             end 
53         fetch next from colroy into @colname    
54         end    
55         close colroy    
56         deallocate colroy 
57      fetch next from tbroy into @tbname    
58     end    
59     close tbroy    
60     deallocate tbroy    
61 end