欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

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

程序员文章站 2022-07-02 19:09:30
寻找一个string所在的表 方法1偶尔会出错   [sql]  --方法1  declare @ssearchcontent varchar(100)='f...

寻找一个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