Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
Log in  ::  Register  ::  Not logged in

Get your favorite SSC scripts directly in SSMS with the free SQL Scripts addin. Search for scripts directly from SSMS, and instantly access any saved scripts in your SSC briefcase from the favorites tab.
Download now (direct download link)

Search all tables for text

By Luis Chiriff,

There have been many times I've been looking for a string or a mention of a string inside of a table, sometimes remembering what field it could be stored in is a big task when you have a massive database. I saw some code on this website about a year ago that did a search feature but I felt it didn't do it the way I wanted, so I cooked up my own version that at the end provides me with a list of all the tables that the string was found in.


This searches all the text/string type fields in the table, so if you had a table that had 6 fields, 5 varchar's and 1 int, it would search the 5 varchars individually, ie


select * from MyTable where StringField1 like '%query%' or StringField2 like '%query%' or StringField3 like '%query%' or StringField4 like '%query%' or StringField5 like '%query%'


To use it:

Step 1. Change the word "territory" to the word you are looking for.

Step 2. Set the return results to Text (it can run in Grid mode however it Management Studio will generate too many errors as it returns the results) or CTRL+T

Step 3. Execute

As shown in the image, you'll see the select statement it used to find that data. What fields were queried, and if the generated string is too long (tables with really long field names) it breaks it into extra SQL statements that it runs individually also.


This script is still under development but I've often found this to be the case when writing SQL, you'll always find a better way of doing it later on. It was written under SQL 2005 and am pretty sure I have tested it in SQL 2000

I hope that this script is of use to people, I have used it on many occasions and found it to be a treat.




Luis Chiriff

Total article views: 2834 | Views in the last 30 days: 5
Related Articles

String Search

Search for a string value within columns of data types CHAR, NCHAR, NTEXT, NVARCHAR, TEXT, VARCHAR, ...


search all fields at once

search all fields at once


Object Search

Procedure to search any database (or a combination of databases, including all) for a specific strin...


search string help needed...

search string help needed...

find string    
find text    
locate string    

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones