CODEWITHSUNDEEP
sql-serversql-server-2008
ajma
ajma

Reputation: 12206

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

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.

Upvotes: 3

Views: 5482

Answers (6)

AlexCuse
AlexCuse

Reputation: 18296

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

Upvotes: 1

Nathan Koop
Nathan Koop

Reputation: 25197

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';

Upvotes: 3

Daniel Br&#252;ckner
Daniel Br&#252;ckner

Reputation: 59645

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.

Upvotes: 0

Quassnoi
Quassnoi

Reputation: 425321

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.

Upvotes: 0

casperOne
casperOne

Reputation: 74530

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).

Upvotes: 1

Pablo Santa Cruz
Pablo Santa Cruz

Reputation: 181280

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

Upvotes: 0

Related Questions