I was asked what sounded like a fairly simple question today - is there a way to do a full text search of a database, without having to set up full text searching. As this was to answer an immediate query - where was some text that was being displayed coming from - and not within the context of a query to form part of an application I thought about it a while, and decided that some dynamic SQL was what was needed. I started off with the fact that I wanted to pull together a statement like:
SELECT COUNT(*) FROM TableName WHERE ColumnName LIKE '%SearchTerm%'
and then run that across all columns which are text, ntext, varchar, nvarchar, char or nchar.

Getting the list of table and column names is reasonably straightforward
SELECT TABLE_NAME,COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE DATA_TYPE IN ('varchar', 'nvarchar', 'text', 'ntext', 'char', 'nchar')

I wanted to loop through all of these and get the counts, so I used a local table variable to hold the results
DECLARE @tabSearchableColumns TABLE (TableName VARCHAR(100), ColumnName VARCHAR(100), Matches int) and simply inserted the results of the above query into it (along with a NULL for the Matches column). I then set up a loop, looping based on the COUNT(*) FROM @tabSearchableColumns WHERE Matches IS NULL. Within that loop I take the top item from @tabSearchableColumns, and use the TableName and ColumnName to populate the SQL SELECT above.

Now came the interesting part, getting the variable @intDataCount from the following statement:
EXEC (SELECT @intDataCount = COUNT(*) FROM TableName WHERE ColumnName LIKE '%SearchTerm%)
as the variable @intDataCount has the scope of the execute statement, and doesn't get propogated to any outside variable with the same name.

To get around this involves the use of sp_executesql specifying the parameter that we expect to get out, i.e. EXEC sp_executesql @strSQL, N'@intDataCount INT OUTPUT', @intDataCount OUTPUT

An important point here is that @strSQL, or the string being sent in as the SQL string MUST be cast be an NVARCHAR, either by DECLARE @strSQL NVARCHAR(100) or N'SELECT ColumnName FROM TableName'. Otherwise, don't be surprised to encounter the following error:
Msg 214, Level 16, State 2, Procedure sp_executesql, Line 1
Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'.

Finally, having got the count out into my @intDataCount variable, I can update the appropriate row in tabSearchableColumns, redo the COUNT(*) FROM @tabSearchableColumns WHERE Matches IS NULL and continue looping. The final stage is just to select the table and column data which has Matches > 0.

The script for FindTableColumnDataMatches is available for download, and as with sp_generateinsert is tested against SQL Server 2005, leave me comments if you've found this useful, or have suggestions for improvements.