question

This is a quick update of an [ugly query][1] I wrote a number of years ago for SQL 2000. It's not super beautiful, but it does almost exactly what you are looking for.

This updated version searches all tables and character based columns in a database and returns the schema.table_name, the search term, the number of matching rows in the table, and the search query for each table with matching rows. The search query lists all of that table's character columns in the WHERE clause in an OR list.

Once it gives you the table(s) with matching rows, you just copy the source query out of the results and change it to a SELECT * to get a quick look at matching rows, including the matched column(s).

I just ran the query below against AdventureWorks and it showed that Production.ProductDescription has 27 rows that contain the word 'aluminum' and Production.ProductReview has two rows with the same word. A quick conversion of the source query contained in the result set to SELECT * shows the 'descripton' and 'comments' are the matching columns.

**EDIT**

I added one more column containing another query to the result set. This new column is a query that lists all of the character based columns in the table along with the number of rows for which that columnsh contains a match for the search term. The results now look like this:

![alt text][2]

Just copy the entire `column_analysis_query_text` column and execute it from a query window. No more converting to a SELECT * and inspecting to find the matching columns. You'll now see your table name, search term, and a summary of which columns have how many hits, like this result of the aforementioned AdventureWorks results:

![alt text][3]

This is more along the lines of what I always wanted it to do. Thanks again for the impetus to make this change!!

**END EDIT**

I look forward to seeing other solutions posted here!

    DECLARE @searchString nvarchar(100)
    
    SET @searchString = '%aluminum%'
    
    SET NOCOUNT ON
    
    CREATE TABLE ##search_results (result_table nvarchar(1000), search_term NVARCHAR(100), result_count bigint)
    CREATE TABLE ##search_queries (result_table nvarchar(1000), query_text NVARCHAR(MAX), column_analysis_query_text nvarchar(max))
    
    DECLARE @tbl sysname, @schema sysname
    DECLARE @prevTbl sysname, @prevSchema sysname
    
    SELECT TOP 1 
        @tbl = so.name, 
        @schema = ss.name 
    FROM 
        sysobjects AS so 
        INNER JOIN sys.schemas AS ss 
            ON so.uid = ss.schema_id 
        INNER JOIN syscolumns AS sc 
            ON so.id = sc.id 
        INNER JOIN systypes AS st 
            ON sc.xtype = st.xtype
    WHERE 
        so.type = 'u'
        AND st.name IN ('char', 'varchar', 'nchar', 'nvarchar', 'text', 'ntext')
    ORDER BY 
        quotename(ss.name) + '.' + quotename(so.name) DESC
        --order by so.name desc
    
    DECLARE @sql nvarchar(max), @where nvarchar(max), @column_analysis_sql nvarchar(max), @column_analysis_list nvarchar(max)
    
    WHILE @tbl IS NOT NULL AND @schema IS NOT NULL
    
    BEGIN
    
        SET @where = ''
    	SET @column_analysis_list = ''
    	
        SELECT 
            @where = @where + ' OR ' + quotename(sc.name) + ' LIKE ''' + @searchString + '''',
            @column_analysis_list = @column_analysis_list + ', sum(CASE WHEN ' + quotename(sc.name) + ' LIKE ''' + @searchString + ''' THEN 1 ELSE 0 END) AS ' + quotename(sc.name) 
        FROM
            sysobjects AS so
            INNER JOIN sys.schemas AS ss
                ON so.uid = ss.schema_id
            INNER JOIN syscolumns AS sc 
                ON so.id = sc.id 
            INNER JOIN systypes AS st 
                ON sc.xtype = st.xtype
        WHERE so.type = 'u'
            AND st.name IN ('char', 'varchar', 'nchar', 'nvarchar', 'text', 'ntext')
            AND ss.name = @schema
            AND so.name = @tbl
    
        -- get rid of the initial ' OR ' and initial ','
    
        SET @where = substring(@where, 4, len(@where))
        SET @column_analysis_list = substring(@column_analysis_list, 2, len(@column_analysis_list))
        
        SET @sql = 'SELECT ' + quotename(quotename(@schema) + '.' + quotename(@tbl), '''') + ' AS resultsTable, ' + quotename(@searchString, '''') + ' AS searchTerm, count(*) AS resultsCount FROM ' + quotename(@schema) + '.' + quotename(@tbl) + ' WHERE '
    	SET @column_analysis_sql = 'SELECT ' + quotename(quotename(@schema) + '.' + quotename(@tbl), '''') + ' AS resultsTable, ' + quotename(@searchString, '''') + ' AS searchTerm, ' + @column_analysis_list + ' FROM ' + quotename(@schema) + '.' + quotename(@tbl) + ' WHERE '
        
        INSERT ##search_queries (result_table, query_text, column_analysis_query_text) VALUES (quotename(@schema) + '.' + quotename(@tbl), @sql + @where, @column_analysis_sql + @where)
    
        SET @sql = 'INSERT ##search_results ' + @sql + @where
    
        EXEC sp_executesql @statement = @sql
        SELECT @prevTbl = @tbl, @prevSchema = @schema
        SELECT TOP 1
            @tbl = so.name, 
            @schema = ss.name 
        FROM 
            sysobjects AS so 
            INNER JOIN sys.schemas AS ss 
                ON so.uid = ss.schema_id 
            INNER JOIN syscolumns AS sc 
                ON so.id = sc.id 
            INNER JOIN systypes AS st 
                ON sc.xtype = st.xtype
        WHERE
            so.type = 'u'
            AND st.name IN ('char', 'varchar', 'nchar', 'nvarchar', 'text', 'ntext')
            --AND so.name < @tbl
            AND quotename(ss.name) + '.' + quotename(so.name) < QUOTENAME(@schema) + '.' + QUOTENAME(@tbl)
        ORDER BY 
            quotename(ss.name) + '.' + quotename(so.name) DESC
            --order by so.name desc
    
        IF @prevTbl = @tbl AND @prevSchema = @schema
        BEGIN
            SELECT @tbl = NULL, @schema = NULL
        END
    END
    
    SELECT 
        sr.result_table, sr.search_term, sr.result_count, sq.query_text, sq.column_analysis_query_text
    FROM 
        ##search_results AS sr 
        INNER JOIN ##search_queries AS sq 
            ON sr.result_table = sq.result_table 
    WHERE
        sr.result_count > 0 
    ORDER BY 
        sr.result_count DESC, 
        sr.result_table DESC
    
    DROP TABLE ##search_results
    DROP TABLE ##search_queries

[1]: http://kenj.blogspot.com/2009/11/finding-something-somewhere-in-database.html95 [2]: /upfiles/full_database_search_results.png [3]: /upfiles/full_database_search_details.png