CODEWITHSUNDEEP
sqlsql-serversql-server-2005t-sql
LoveMeSomeCode
LoveMeSomeCode

Reputation: 3947

SQL Super Search

Does anyone have a good method for searching an entire database for a given value? I have a specific string I'm looking for, it's in TableA, and it's also a FK to some other table, TableB, except I don't know which table/column that is.

Assuming there's a jillion tables and I don't want to look through them all, and maybe will have to do this in several different cases, what would be the best way?

Since I didn't want a Code-SQL bridge, my only all-SQL idea was:

select tablename and column_name from INFORMATION_SCHEMA.COLUMNS

...then use a cursor to flip through all the columns, and for all the datatypes of nvarchar I would execute dynamic SQL like:

SELECT * from @table where @column =  @myvalue

Needless to say, this is slow AND a memory hog.

Anyone got any ideas?

Upvotes: 1

Views: 610

Answers (5)

Steve Kass
Steve Kass

Reputation: 7184

Here's a solution I wrote several years ago: http://www.users.drew.edu/skass/sql/SearchAllTables.sql.txt

Upvotes: 2

KM.
KM.

Reputation: 103697

to dump your data, read up on the bcp Utility

Upvotes: 0

Aye
Aye

Reputation: 393

Just make SP that searches in all relevant columns using OR.

Why don't you know which columns to search on?

If the list of columns is ever-shifting, then you just need to make sure that whatever process results in changing the schema would result in the change in this stored procedure.

If the list of the columns is just too dang big for you to type up inot the SP, use some elementary perl/grep/whatnot to do it in 1 line, e.g for SYBASE.

my_dump_table_schema.pl|egrep "( CHAR| VARCHAR)"|awk '{$1}'|tr "\012" " "|perl -pe '{s/ / = \@SEARCH_VALUE OR /g}'; echo ' = @SEARCH_VALUE'

The last echo is needed to add the value to last column

Upvotes: 0

Ben Gribaudo
Ben Gribaudo

Reputation: 5147

Here are a couple of links talking about how to do this:

Both of them use the approach you were hoping to avoid. Refine them so that they only searched columns that were foreign keys should improve their performance by eliminating the searching of unnecessary tables.

Upvotes: 3

Andy Ross
Andy Ross

Reputation: 12051

Dump the database and grep?

I guess a more focused question might be: if you don't know how the schema works, what are you going to do with the answer you get anyway?

Upvotes: 6

Related Questions