How to search all columns of all tables in a microsoft sql database for a keyword (including any data type)

Pages: 1 2


I’ve been working on a very large unknown database (over 45 tables) where the reverse engineered schema diagram was quite chaotic. Having the task to find several needles in the haystack I searched the internet and found the very useful script at:  (pasted below).

However, one limitation I noticed was that the script only searched (‘char’, ‘varchar’, ‘nchar’, ‘nvarchar’).

With quick additions to the AND DATA_TYPE IN (‘char’, ‘varchar’, ‘nchar’, ‘nvarchar’)  line, the stored procedure can be tailored to search IDs, and even Binary Large Objects (e.g. PDFs).

For me, I wanted to find where specific IDs are reused (and unfortunately foreign keys were not set) so I change the line to AND DATA_TYPE IN (‘uniqueidentifier’) .

Cheers!  The code below:

Create this procedure in the required database and here is how you run it:

--To search all columns of all tables in Pubs database for the keyword "Computer"
EXEC SearchAllTables 'Computer'

On the next page we display the complete code.

Menelaos Bakopoulos

Mr. Menelaos Bakopoulos is currently pursuing his PhD both at Center for TeleInFrastruktur (CTiF) at Aalborg University (AAU) in Denmark and Athens Information Technology (AIT) in Athens, Greece. He received a Master in Information Technology and Telecommunications Systems from Athens Information Technology and a B.Sc. in Computer Science & Management Information Systems from the American College of Thessaloniki. Since April 2008 he has been a member of the Multimedia, Knowledge, and Web Technologies Group.

More Posts