Today, I needed a way of searching through a database, any database, looking for a certain string. The procedure I had was an old one that used the information_schema and I couldn't figure out how to update it to take in user-types, so I wrote one using system views. It lists the table, the column and the number of rows that contained the string. This is the 'brute-force' search, where one just finds every possible column that could contain a string and searching it with a LIKE expression. This is something that one hopes is never necessary in a 'well-ordered universe' but I occasionally need to do it just to find where a certain string is being held. It is slow. Even on Adventureworks, it takes a minute on my test machine for the first search (far quicker with subsequent searches). On the live database that I was examining (not one of mine, I hasten to add) it took three minutes, but it was a lot faster than any other way I could think of. I've left it as a batch rather than procedure in the hope that someone else will find it useful. This particular version doesn't look at CLR types but then I knew the string wasn't stored in a CLR type!
In the example, the @Searchterm is set to look for the tell-tale HREF= of an anchor. You'll need to put all the wildcard characters in
when you change it.
DECLARE @command NVARCHAR(MAX) --the SQL Statement that will be executed
DECLARE @SearchTerm NVARCHAR(100) --the term to be searched
--start by assembling UNION of all rows in each table that can be checked
SELECT @command = COALESCE(@command + '
UNION ALL ', '')--use fact of null in variable to omit first UNION ALL
+ 'SELECT '
+ ''''+SCHEMA_NAME(t.schema_id) + '.'
+ QUOTENAME(REPLACE(t.NAME,'''',''''''))+''''--table name
+ ' AS tableName, COUNT(*) AS numberOfRows, '''
+ QUOTENAME(REPLACE(c.NAME,'''','''''')) + ''' AS columnName
FROM ' + SCHEMA_NAME(t.schema_id) + '.'
+ QUOTENAME(REPLACE(t.NAME,'''',''''''))--table name
+' WHERE '
+ CASE WHEN (ty.name IN ( 'text', 'ntext', 'xml', 'sql_variant')
THEN 'CONVERT(nvarchar(max), '
+ QUOTENAME(REPLACE(c.NAME,'''','''''')) + ')'
WHEN ty.name IN ( 'char', 'nchar', 'varchar','nvarchar')
ELSE '''''' --this must never happen!
+ ' LIKE '''+REPLACE(@searchTerm,'''','''''')+''' '
FROM sys.columns AS c --we search all possible columns
INNER JOIN sys.types AS ty --of particular data types
ON c.user_type_id = ty.user_type_id
INNER JOIN sys.tables AS t --with their tables
ON c.OBJECT_ID = t.OBJECT_ID
WHERE (ty.name IN ('char','nchar','nvarchar','varchar','text',
'ntext', 'xml', 'sql_variant')
OR is_user_defined=1 )
SELECT * from ('+@command+') hits
WHERE hits.numberOfRows > 0'