Search All Tables For Given Value 1

[OrWolf]

I have a value that is displaying in an error message and I need to update it. The problem is that I can't seem to locate the value. Can someone suggest a method to cycle through all of the tables and fields in a database and attempt to find a given value?

Thanks!

 

[stravis]

Is this value alpha or numeric?

 

[mccartrey]

I would like to know how to do this too please.

Let's just say for instance.
one script for text and one script for numerics
unless there is a way to combine them?

 

[AlexCuse]

What is giving you the error message? Is it a stored procedure or a query? Also, what is the specific error message?

I think a better practice for this would be to simply step through your SP and figure out at least which statement is causing the error. Then it is simply a matter of tracking down which involved field is the source of your error.

If it's simply one query, can you post your SQL code?

I would look at char-to-numeric conversions and joins first (if you are sure it is related to a value in the table and not syntax). These tend to be the places where values within the tables get me.

A script for searching each column in each table of your database would probably be a bear to write. Just my two cents. If anyone has a script to do this I too would be interested in seeing it.

Good Luck,

Alex

A wise man once said
"The only thing normal about database guys is their tables".

 

[hvass]

What version are you running.
As of v5 ish you get direct access to information_schema database the database which holds all the details of tables and columns. So a simple perl script could return all columns from a tables or a selection of tables - then you could search all of the returned columns for a particular value..

 

[hvass]

woops scrub my last post I realise I just strayed over from the mysql forum without realising it

 

[mccartrey]

I'm personally running SQL Server 2005.
Is it possible to query this with Perl or that just for MySQL?

regardless, Does anyone know how to query all tables and columns for a particular value?

regards,

McCartrey

regards,

McCartrey

 

[philhege]

(Sorry I don't have access to the script that I use right now.)

As a general approach:

Select distinct table names from INFORMATION_SCHEMA into a table variable. Add a processing field

Loop through the table variable.


For each table name, construct a SQL statement
by COALESCEing the column names from INFORMATION_SCHEMA for the current table name with
'= ' + the value to look for

EXEC the SQL statement.

Mark the table as processed.




Phil Hegedusich
Senior Programmer/Analyst
IIMAK

http://www.iimak.com

-----------
I'll have the roast duck with the mango salsa.

 

[AlexCuse]

McCartrey - I think you're out of luck. Anything you could put together in Perl would probably be much more complex than what you could do in MSSQL.

OrWolf - If you run this in your database, it will give you a list of all user columns (with table name). You could possibly build a script using this as a starting point, but I can't imagine such a script would be very efficient for large db's.

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

So you could do something like this:

declare @i int
declare @tbl varchar(50)
declare @col varchar(50)
declare @sql varchar(200)
declare @out bigint
declare @search varchar(100)


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), FindCnt int)

set @search = '''Some Value'''

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 = 'select [' + @col + '] from [' + @tbl + '] where convert(varchar(500), [' + @col + ']) = ' + @search 
	execute (@sql)
	select @out = @@ROWCOUNT

	insert into #SEARCHRESULT
	select @tbl, @col, @out
	set @i = @i + 1
   End

drop table #TEMP
select * from #SEARCHRESULT where FindCnt > 0
drop table #SEARCHRESULT

Couple notes I can think of-

Make sure when setting @search, that you place extra quotes around it if looking for a text string. Like this:

set @search = '''somevalue'''

There are also some data types that will cause this script to crash. You will need to possibly exclude those types.

Finally, if anyone knows how I can get the execute(@sql) to not display a grid, that would be good to know.

Hope this gets you on track,

Alex

A wise man once said
"The only thing normal about database guys is their tables".

 

[gmmastros]

Alex,

You can get the Execute line to not display a grid if you don't select anything . Seriously, what I usually do is create a temp table, and then insert the values in to the temp table. So, using your code as a basis for this example...

declare @i int
declare @tbl varchar(50)
declare @col varchar(50)
declare @sql varchar(2000)
declare @out bigint
declare @search varchar(100)


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

Insert Into #Temp(colName, tblName)
Select C.Column_Name, C.Table_Name
From   Information_Schema.Tables As T
       Inner Join Information_Schema.Columns As C
         On T.Table_Name = C.Table_Name
Where  T.Table_Type = 'Base Table'
       And C.Data_Type In ('char', 'nchar','varchar','nvarchar')

set @search = '''Administrator'''

set @i = 1

Create Table #Out(TableName varchar(200), FieldName VarChar(200))

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

    set @sql = 'If Exists(select * 
                          from   [' + @tbl + '] 
                          where  convert(varchar(500), [' + @col + ']) = ' + @search + '
                         )
                     Insert Into #Out(TableName, FieldName) Values(''' + @tbl + ''',''' + @col + ''')'
    execute (@sql)
    set @i = @i + 1
   End

drop table #TEMP

Select * From #Out

Drop Table #Out

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[AlexCuse]

You can get the Execute line to not display a grid if you don't select anything

Brilliant!

I guess you don't really need count once you know it's there...

Thanks George

A wise man once said
"The only thing normal about database guys is their tables".

 

[AlexCuse]

OrWolf, mccartrey -

I ended up with this stored procedure (I know I shouldn't use sp as a prefix, but in this case I really wanted to so I could call from other db's)

CREATE proc sp_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

Hope it helps,

Alex

A wise man once said
"The only thing normal about database guys is their tables".