Search all columns of a table for a value?

I’ve looked for an answer to this, but all I can find is people asking how to search all columns of ALL tables in a database for a value. I just want to search all columns for a specific table. The code people have come up with for the all tables question is complicated and hard for me to figure out where exactly it’s searching a specific table. Can somebody help me out? Thanks

  • What is difference between != and <> in sql server
  • SqlServer 08: Query to list all databases in an instance?
  • Syspro - Update query for sales prices
  • Show average execution time
  • SQL Query - Mathematical Query across 2 tables
  • How to make data access layer for SQL Server and MySql
  • 8 Solutions collect form web for “Search all columns of a table for a value?”

    Just use some third party tool. There are several that are 100% free and you can’t go wrong with any of these because they will save you a ton of time.

    ApexSQL Search (searches both schema and data), SSMS Toolpack (searches schema and data but not free for SQL Server 2012), SQL Search (searches data only).

    Frankly, I don’t really understand why even very experienced DBAs bother writing scripts for this if they can use some tool for free that will do the job.

    I have no idea of the column types or data values you’re searching for, but I’d guess you’re trying to search for a substring among multiple text columns.

    This is a job for Full-Text Search.

    Don’t waste time with LIKE '%' + @SearchStr + '%'. You have to write a lot of complicated code to support it, and that solution won’t perform well anyway.

    In a similar question I mentioned SQL Workbench/J.

    The command that searches the database can also be limited to just one table. So even if that question was PostgreSQL specific, the tool works for SQL Server as well as far as I know.

    Here is a solution that, like @Decker97’s approach, figures out from metadata which columns are eligible for text search. Assumes 2005+ for use of XML PATH as well as sys.columns, sys.tables, etc. Supports TEXT/NTEXT/CHAR/NCHAR/VARCHAR/NVARCHAR, and even puts the leading N on the search string where appropriate. Does not support XML columns. What it does do slightly differently is that it returns a single resultset for each table, not for every single column, so you only get one row per actual table row even if multiple columns match. If the goal is to understand how it works rather than simply having a solution, it will probably take a bit more than this… perhaps I should blog about this problem (I should probably not be lazy and actually build the column lists instead of just using SELECT *).

    DECLARE @SearchTerm NVARCHAR(32) = 'foo';
    
    DECLARE @TableName NVARCHAR(128) = NULL;
    
    SET NOCOUNT ON;
    
    DECLARE @s NVARCHAR(MAX) = '';
    
    WITH [tables] AS
    (
        SELECT [object_id] 
            FROM sys.tables AS t
            WHERE (name = @TableName OR @TableName IS NULL)
            AND EXISTS
            (
                SELECT 1 
                    FROM sys.columns
                    WHERE [object_id] = t.[object_id]
                    AND system_type_id IN (35,99,167,175,231,239)
            )
    )
    SELECT @s = @s + 'SELECT ''' 
        + REPLACE(QUOTENAME(OBJECT_SCHEMA_NAME([object_id])),'''','''''')
        + '.' + REPLACE(QUOTENAME(OBJECT_NAME([object_id])), '''','''''')
        + ''',* FROM ' + QUOTENAME(OBJECT_SCHEMA_NAME([object_id]))
        + '.' + QUOTENAME(OBJECT_NAME([object_id])) + ' WHERE ' + 
        (
            SELECT QUOTENAME(name) + ' LIKE ' + CASE 
                WHEN system_type_id IN (99,231,239) 
                THEN 'N' ELSE '' END
                + '''%' + @SearchTerm + '%'' OR '
            FROM sys.columns
            WHERE [object_id] = [tables].[object_id]
            AND system_type_id IN (35,99,167,175,231,239)
            ORDER BY name
            FOR XML PATH(''), TYPE
    ).value('.[1]', 'nvarchar(max)') + CHAR(13) + CHAR(10)
    FROM [tables];
    
    SELECT @s = REPLACE(@s,' OR ' + CHAR(13),';' + CHAR(13));
    
    /*
        make sure you use Results to Text and adjust Tools / Options / 
        Query Results / SQL Server / Results to Text / Maximum number
        of characters if you want a chance at trusting this output 
        (the number of tables/columns will certainly have the ability
        to exceed the output limitation)
    */
    
    SELECT @s;
    -- EXEC sp_executeSQL @s;
    

    I modified this stored proc to take a table name as the second parameter and just search that table for the data:

    IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SearchOneTable]') AND type in (N'P', N'PC'))
    DROP PROCEDURE [dbo].[SearchOneTable]
    GO
    
    SET ANSI_NULLS ON
    GO
    
    SET QUOTED_IDENTIFIER ON
    GO
    
    CREATE PROC [dbo].[SearchOneTable]
    (
        @SearchStr nvarchar(100) = 'A',
        @TableName nvarchar(256) = 'dbo.Alerts'
    )
    AS
    BEGIN
    
        CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))
    
        --SET NOCOUNT ON
    
        DECLARE @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
        SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')
        --SET @SearchStr2 = QUOTENAME(@SearchStr, '''') --exact match
        SET @ColumnName = ' '
    
    
            WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
            BEGIN
                SET @ColumnName =
                (
                    SELECT MIN(QUOTENAME(COLUMN_NAME))
                    FROM    INFORMATION_SCHEMA.COLUMNS
                    WHERE       TABLE_SCHEMA    = PARSENAME(@TableName, 2)
                        AND TABLE_NAME  = PARSENAME(@TableName, 1)
                        AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
                        AND QUOTENAME(COLUMN_NAME) > @ColumnName
                )
    
                IF @ColumnName IS NOT NULL
                BEGIN
                    INSERT INTO #Results
                    EXEC
                    (
                        'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630) 
                        FROM ' + @TableName + ' (NOLOCK) ' +
                        ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
                    )
                END
            END 
        SELECT ColumnName, ColumnValue FROM #Results
    END
    
    
    GO
    

    This sounds like you just want to know which table and column some data is stored, not that you want to know that during the execution of your code, or change it. I also had this problem and this solved it:

    Download your database in SQL format (using phpmyadmin, for example), open it with a text editor and search for the occurrences you want.

    Cutesie little work-around that involves a bit more typing OR Excel snipping/formatting:

    Invert the IN operator in a WHERE clause as VALUE IN <fields> (as opposed to the more common use case of FIELD IN <values>).

    SELECT col_1, col_2, ... , col_n 
    FROM <table>
    WHERE CAST(<value> AS varchar(max)) IN 
       (
       CAST(col_1 AS varchar(max)),
       CAST(col_2 AS varchar(max)),
       ...,
       CAST(col_n AS varchar(max))
       )
    

    Since varchar is a pretty malleable data type, this becomes pretty foolproof (you can throw ISNULL/NULLIF to modify as needed), and depending on the use case can probably be used across more than one search value.

    I have come across this issue, normally after uploading data from a CSV file where I had to modify the commas ‘,’ in text fields so the data would load properly & once in SQL Server, the need comes to change the modified character back to a comma & it’s helpful to be able to search the entire table. Greg Robidoux at mssqltips has posted a Stored Procedure that does just this, searches the Columns of a specified Table for a particular String value. You can find it along with a SPROC that does not use the cursor & more details here:

    https://www.mssqltips.com/sqlservertip/1522/searching-and-finding-a-string-value-in-all-columns-in-a-sql-server-table/

    I have posted the original SPROC below:

    USE master 
    GO 
    
    CREATE PROCEDURE dbo.sp_FindStringInTable @stringToFind VARCHAR(100), @schema sysname, @table sysname 
    AS 
    
    DECLARE @sqlCommand VARCHAR(8000) 
    DECLARE @where VARCHAR(8000) 
    DECLARE @columnName sysname 
    DECLARE @cursor VARCHAR(8000) 
    
    BEGIN TRY 
       SET @sqlCommand = 'SELECT * FROM [' + @schema + '].[' + @table + '] WHERE' 
       SET @where = '' 
    
       SET @cursor = 'DECLARE col_cursor CURSOR FOR SELECT COLUMN_NAME 
       FROM ' + DB_NAME() + '.INFORMATION_SCHEMA.COLUMNS 
       WHERE TABLE_SCHEMA = ''' + @schema + ''' 
       AND TABLE_NAME = ''' + @table + ''' 
       AND DATA_TYPE IN (''char'',''nchar'',''ntext'',''nvarchar'',''text'',''varchar'')' 
    
       EXEC (@cursor) 
    
       OPEN col_cursor    
       FETCH NEXT FROM col_cursor INTO @columnName    
    
       WHILE @@FETCH_STATUS = 0    
       BEGIN    
           IF @where <> '' 
               SET @where = @where + ' OR' 
    
           SET @where = @where + ' [' + @columnName + '] LIKE ''' + @stringToFind + '''' 
           FETCH NEXT FROM col_cursor INTO @columnName    
       END    
    
       CLOSE col_cursor    
       DEALLOCATE col_cursor  
    
       SET @sqlCommand = @sqlCommand + @where 
       PRINT @sqlCommand 
       EXEC (@sqlCommand)  
    END TRY 
    BEGIN CATCH 
       PRINT 'There was an error. Check to make sure object exists.'
       PRINT error_message()
    
       IF CURSOR_STATUS('variable', 'col_cursor') <> -3 
       BEGIN 
           CLOSE col_cursor    
           DEALLOCATE col_cursor  
       END 
    END CATCH 
    
    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.