SQL Super Search

Question

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?

Answer 1

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?

Answer 2

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

• http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/the-ten-most-asked-sql-server-questions--1#2
• http://vyaskn.tripod.com/search_all_columns_in_all_tables.htm

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.

Answer 3

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

Answer 4

to dump your data, read up on the bcp Utility

Answer 5

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