SQL Super Search
Does anyone have a good method to find the entire database for a given value? I have a specific row that I am looking for, it is in TableA as well as an FK for another table, TableB, except that I do not know which table / column is.
Assuming a jillion table exists and I don't want to go through all of them, and may have to do this on several different occasions, what would be the best way?
Since I don't need a Code-SQL bridge, my only all-SQL idea is:
select tablename and column_name from INFORMATION_SCHEMA.COLUMNS
... then use a cursor to cycle through all columns, and for all nvarchar datatypes, I would execute dynamic SQL like:
SELECT * from @table where @column = @myvalue
Needless to say, this is a slow And raging memory.
Does anyone have any idea?
source to share
Here are some links on 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 take the approach you were hoping to avoid. Refine them so that they only look for columns that were foreign keys to improve their performance by eliminating unnecessary table lookups.
source to share
Here is a solution I wrote years ago: http://www.users.drew.edu/skass/sql/SearchAllTables.sql.txt
source to share
Just make an SP that searches all matching columns using OR.
Why don't you know which columns to look for?
If the list of columns is constantly changing, you just need to make sure that any process that changes the schema will change that stored procedure.
If the list of columns is too long for you to type inot SP, use some rudimentary perl / grep / whatnot to do it in 1 line, like 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 needs to be added for the last column
source to share