数据库寻找一个string所在的表

寻找一个string所在的表

方法1偶尔会出错

 

[sql] 
--方法1 
declare @ssearchcontent varchar(100)='f6f89834-817c-47d7-a436-6484754111cb' 
----------------------------------------------------------------------------------------------- 
declare @table table(id int identity(1,1),columnname varchar(100),tablename varchar(100)) 
declare @line int = 1 
declare @total int 
declare @sql nvarchar(4000) 
declare @columnname varchar(100) 
declare @tablename varchar(100) 
declare @num int  
 
insert into @table 
select a.name,object_name(a.object_id) as tablename from sys.columns as a inner join sysobjects as b on a.object_id = b.id and b.xtype = 'u' and system_type_id = 167 
 
select @total = count(1) from @table 
 
while @line <= @total 
begin 
    select @columnname = columnname,@tablename = tablename 
    from @table  
    where id = @line 
     
    --set @sql='select @a=count(*) from ' + @tablename + ' where ' + @columnname + ' = ''刘八'''  
      set @sql='select @a=count(*) from ' + @tablename + ' where ' + @columnname + ' = ''' + @ssearchcontent + '''' 
    exec sp_executesql @sql,n'@a int output',@num output  
     
    if @num > 0 
    begin 
        select @tablename as tablename,@columnname as columnname 
    end 
     
    set @line = @line + 1 
end 
----------------------------------------------------------------------------------------------- 
 
--方法2 
declare @str varchar(100)  
 set @str='f6f89834-817c-47d7-a436-6484754111cb' 
---------------------------------------------------------------------------------------------- 
 declare @s varchar(8000)  
 declare tb cursor local for  
     select 'if exists(select 1 from ['+b.name+'] where ['+a.name+'] like ''%'+@str+'%'')  
         print '' ['+b.name+'].['+a.name+']'''  
     from syscolumns a join sysobjects b on a.id=b.id  
     where b.xtype='u' and a.status>=0  
         and a.xusertype in(175,239,231,167)  
 open tb  
 fetch next from tb into @s  
 while @@fetch_status=0  
 begin  
     exec(@s)  
     fetch next from tb into @s  
 end  
 close tb  
 deallocate tb 
---------------------------------------------------------------------------------------------- 
作者：keenweiwei