How to write an SQL query that looks up the value in all columns and all tables in my database?

Question

I am trying to find a value in my Microsoft SQL Server 2008 database, but I do not know which column or table to look for. I am trying to create a query that will only look in all tables and all columns for my value.

Answer 1

You could probably do this using dynamic sql using sys.cols and sys.tables, which you should be able to create a query.

This, in any case, will be an extremely long request.

, , sql, , , , . [ ] . , varchar.

SELECT 'SELECT ''' + TABLE_NAME + '.' + column_name + 
   ''' FROM ' + TABLE_NAME + ' WHERE ' + 
   column_name + ' = ''[your value here]'''
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE DATA_TYPE = 'varchar';

Answer 2

. sys.tables sys.columns ( ), OR ( ).

Answer 3

, , . , , sp_msForEachTable! (, MAX, 2005 )

create proc SearchForValues (@search varchar(100))

as

Begin

declare @i int
declare @tbl varchar(50)
declare @col varchar(50)
declare @sql varchar(500)


create table #TEMP (id int identity (1,1), colname varchar(50), tblname varchar(50))

insert into #TEMP
select a.name, b.name from dbo.syscolumns a inner join
(
select * from dbo.sysobjects where xtype = 'U'
) b
on a.ID = b.ID

create table #SEARCHRESULT (TblName varchar(50), ColName varchar(50))


If isnumeric(@search) = 0 and @search is not null
begin
set @search = '''' + @search + ''''
end

set @i = 1

While @i <= (select max(id) from #TEMP)
   Begin
    select @tbl = tblname from #temp where ID = @i
    select @col = colname from #temp where ID = @i

    set @sql = 'If Exists(select *
                          from   [' + @tbl + ']
                          where  convert(varchar(500), [' + @col + ']) = ' + @search + '
                         )
                     Insert Into #SEARCHRESULT (TblName, ColName) Values(''' + @tbl + ''',''' + @col + ''')'

    execute (@sql)

    set @i = @i + 1
   End

drop table #TEMP
select * from #SEARCHRESULT
drop table #SEARCHRESULT
end

Answer 4

SQL. , (, PL/SQL Oracle).

Answer 5

1st april, , grep .

Answer 6

sp_msForEachTable . . . , , . , , , .

, , . , . SQL. , , . , .