SQL Clone
SQLServerCentral is supported by Redgate
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: 2866 | Views in the last 30 days: 2
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...


String Search

This script performs a smart search against all fields of a database for a given "String." The TSQL...

find string    
find text    
locate string