I was recently asked if I could do a quick search on a SQL Server database to find any references to a particular network folder. A user
application generated a set of files which were stored at this location. The user wanted to move the files and repoint the application
at the new location by updating any database columns that referred to the old location.
He was a bit surprised when I said that a search facility wasn’t available in SQL Server. I can see his logic – after all
databases are all about searching. However, as we know, it’s the application that is usually responsible for implementing
SQL code for whatever searching is required.
That set me thinking as to how I could write some SQL that would query all string columns in all tables in a database
to find a specific string, and return how many matches there were in each column.
This is the solution I came up with :
Enter the string to be searched for here :
@SQL = @SQL +
'SELECT CONVERT(VARCHAR(MAX),COUNT(*)) + ''
matches in column ''+'''
on table '' + ''' + SC.name
+ '.' +
''' [Matches for '''+@SearchString+''':]
FROM ' +
+ '.' +
+ ' WHERE '
' LIKE ''%'
'%'' HAVING COUNT(*)>0 UNION ALL '
Strip off the last UNION ALL
If I run this SQL against a database in SQL Server Management Studio it will search all string based columns (varchar, nvarchar, text, ntext, char, nchar) in
all tables in the current database, for the specified string (in this case 'bracket').
When run against the AdventureWorksLT2008R2 database (one of the Microsoft sample databases available for download from the CodePlex website)
it returns the following results :
As you can see it returns the number of matches on each column where there is at least one match. I've been using this script for a while and
it works in all of the databases that I've used (mostly SQL 2005, 2008 and
2008R2). It is schema aware, so will check schemas other than 'dbo'. It'll also work with user defined types. However I'm
sure there are situations where it won’t work - XML datatypes for one.
Obviously for production databases you need to use this script with care, especially if there are a lot of columns to
search. In that situation it might be better to use a lightweight profiler trace, and see how the application is
getting hold of the data.
Please let me know if you find the script of use.