How do I write an SQL query which looks for a value in all columns and all tables in my database?

Question

Hello. I'm trying to look for a value in my Microsoft SQL Server 2008 database but I don't know what column or table to look in. I'm trying to craft a query which will just look in all tables and all columns for my value.

Answer 1

You can't with plain SQL. Unless you use a tool that does that (such a PL/SQL developer for Oracle).

Answer 2

You can't do it in a single query. You are going to have to cycle through the sys.tables and sys.columns info views and construct multiple queries (a single one for each table) which will look in all the fields for your value in a very long OR construct (one for each field).

Answer 3

I'm risking to be downvoted on this nice 1st april day, but I think it will easier to grep the datafile in this case.

Answer 4

You probably could do it using dynamic sql using sys.cols & sys.tables you should be able to create the query.

This will, in all likelyhood, be an extremely long running query.

I rethought my answer and if you run the query below it will generate a number of sql statements, if you run those statements you will find out which column has the value you want. Just replace [your value here] with the appropriate value. This is assuming your value is a 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 5

The stored procedure sp_msForEachTable executes a query for each table. This is the simple part. Looking into all columns of every table should be the much harder part. At first, they probably have different data types. So you will probably be only able to perform a string comparison.

But I am quit sure that this is posible by using information from the system tables and some system stored procedures. I would try finding a solution to access a single column on a single table where table name and column name are only given as string parameters. At this point Dynamic SQL comes to mind. If you solved that, it should become relativly simple to get all table names with all column names from the system tables and join every together or put it into a stored procedutre. I would like to see the result if you find a solution.

Answer 6

I wrote this a while back, not exactly sure what it was for anymore. I remember it was before I knew about sp_msForEachTable though! You might need to adjust the variable sizes (may as well make them all MAX if you are on 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 7

You have 3rd party tools for things like finding and filtering your data, like this Query tool that can work with different kind of databases.