搜索数据库里面所有表的所有字段内容sql

[ylbai520]

搜索数据库Testdb
数据库里有aa，bb，cc。。。。等表
搜索出字段内容包含‘123’连续字符串的表名和字段
如：aa数据为
a1 a2 a3
1 123 ww


sql的结果为aa表的a2字段上包含123

[nzperfect]

遍历所有表字段，动态拼sql.

[ylbai520]

请给出高效的sql，谢谢

[nzperfect]

先试试这个：

declare  @t  varchar(255),@c  varchar(255)  
declare  table_cursor  cursor  for  
select  a.name,b.name  from  sysobjects  a,syscolumns  b  
where  a.id=b.id  and  a.xtype='u' 
open  table_cursor  fetch  next  from  table_cursor  
into  @t,@c  
while(@@fetch_status=0)  
begin  
    exec(' if exists(select top 1 * from  [' + @t + ']  where  [' + @c + '] like ''%123%'') select * from [' + @t + ']  where  [' + @c + '] like ''%123%''')
    fetch  next  from  table_cursor  into  @t,@c  
end
close  table_cursor  deallocate  table_cursor;

问一下你要查哪些类型的字段？？

[utpcb]

select t1.name,t3.name from sys.tables t1,---sys.system_objects t2,
sys.columns t3

where t1.object_id=t3.object_id
and (t1.name like '%123%' )or (t1.name like '%123%')

[utpcb]

select distinct t1.name,t2.name from sys.tables t1,
sys.columns t2

where t1.object_id=t2.object_id
and (t1.name like '%123%' )or (t2.name like '%123%')

[中国风]

declare @Condition nvarchar(100)
set @Condition=123
declare T_cursor cursor local for
select top 100 ID,Name from	sysobjects where xtype='U' --取一100個表
declare @object_id int,@TabName sysname,@SQL nvarchar(1000)
open T_cursor
fetch next from T_cursor into @object_id,@TabName
while @@fetch_status=0
begin
	select @SQL=isnull(@SQL+' and ','')+'rtrim('+quotename(Name)+')='+quotename(@Condition,'''') from syscolumns where ID=@object_id and
	xuserType in(select xuserType from systypes where Name in('tinyint','smallint','int','bigint','varchar','char','nvarchar','nchar'))
	set @SQL=N' select Tab='''+@TabName+''',* from '+@TabName+' where '+@SQL
	exec(@SQL)
	fetch next from T_cursor into @object_id,@TabName
	set @SQL=null
end
close T_cursor
deallocate T_cursor

[nzperfect]

--drop table #
declare @t  varchar(255),@c  varchar(255) 
--name是满足条件的表 cols是满足条件的列
create table # (name varchar(256),cols varchar(4000)) 
declare  table_cursor  cursor  for  
select  a.name,b.name  from  sysobjects  a,syscolumns  b  
where  a.id=b.id  and  a.xtype='u' 
open  table_cursor  fetch  next  from  table_cursor  
into  @t,@c  
while(@@fetch_status=0)  
begin  
    exec(' 
    set nocount on
    if exists(select top 1 '+@c+' from  [' + @t + ']  where  [' + @c + '] like ''%123%'') 
    begin 
        if not exists(select 1 from # where name='''+@t+''')
            insert into # select '''+@t+''','''+@c+'''
        else
            update # set cols=cols+'','+@c+''' where name='''+@t+'''
        --select '+@c+' from [' + @t + ']  where  [' + @c + '] like ''%123%''
    end    
    ')
    fetch  next  from  table_cursor  into  @t,@c  
end
close  table_cursor  deallocate  table_cursor;
select * from #

[ylbai520]

使用convert函数和排除一些类型就ok了，谢谢，给分