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
SELECT @SearchTerm='%href%'
--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')
                      OR is_user_defined=1)                    
                   THEN 'CONVERT(nvarchar(max), '
                        + QUOTENAME(REPLACE(c.NAME,'''','''''')) + ')'
               WHEN ty.name IN ( 'char', 'nchar', 'varchar','nvarchar')  
                   THEN QUOTENAME(REPLACE(c.NAME,'''',''''''))
               ELSE '''''' --this must never happen!
          END
       + ' 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  @command='
SELECT * from ('+@command+') hits
WHERE hits.numberOfRows > 0'
EXECUTE (@command)