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 :
DECLARE
@SQL VARCHAR(MAX)
DECLARE
@SearchString VARCHAR(100)
SET
@SQL=''
--
------------------------------------------
--
Enter the string to be searched for here :
--
SET
@SearchString='bracket'
--
------------------------------------------
SELECT
@SQL = @SQL +
'SELECT CONVERT(VARCHAR(MAX),COUNT(*)) + ''
matches in column ''+'''
+ C.name
+
'''+''
on table '' + ''' + SC.name
+ '.' +
T.name +
''' [Matches for '''+@SearchString+''':]
FROM ' +
QUOTENAME(SC.name)
+ '.' +
QUOTENAME(T.name)
+ ' WHERE '
+ QUOTENAME(C.name)
+
' LIKE ''%'
+
@SearchString +
'%'' HAVING COUNT(*)>0 UNION ALL '
+CHAR(13)
+ CHAR(10)
FROM
sys.columns
C
JOIN
sys.tables
T
ON
C.object_id=T.object_id
JOIN
sys.schemas
SC
ON
SC.schema_id=T.schema_id
JOIN
sys.types
ST
ON
C.user_type_id=ST.user_type_id
JOIN
sys.types
SYST
ON
ST.system_type_id=SYST.user_type_id
AND
ST.system_type_id=SYST.system_type_id
WHERE
SYST.name IN
('varchar','nvarchar','text','ntext','char','nchar')
ORDER
BY T.name,
C.name
--
Strip off the last UNION ALL
IF
LEN(@SQL)>12
SELECT
@SQL=LEFT(@SQL,LEN(@SQL)-
12)
EXEC(@SQL)
--PRINT
@SQL
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.