How do I find in which table Data has my given value in SQL

Question

What I need is to find out which tables have certain column data. It doesn't matter which column it belongs in, I just don't know which table to look at since there are a lot of tables.

Please let me know if you have ideas. Thanks.

Note: I should search in XML columns as well.

This is what I have tried. It did not work, please suggest better ideas for me to try.

    USE DATABASE_NAME
DECLARE @SearchStr nvarchar(100) = 'SEARCH_TEXT'
DECLARE @Results TABLE (ColumnName nvarchar(370), ColumnValue nvarchar(3630))

SET NOCOUNT ON

DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
SET  @TableName = ''
SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')

WHILE @TableName IS NOT NULL

BEGIN
    SET @ColumnName = ''
    SET @TableName = 
    (
        SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
        FROM     INFORMATION_SCHEMA.TABLES
        WHERE         TABLE_TYPE = 'BASE TABLE'
            AND    QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
            AND    OBJECTPROPERTY(
                    OBJECT_ID(
                        QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
                         ), 'IsMSShipped'
                           ) = 0
    )

    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', 'int', 'decimal')
                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    
END

SELECT ColumnName, ColumnValue FROM @Results

` It did not work` means what? By the way, aggregation using a scalra variable is not safe, it is not guaranteed to work in the way you expect, use `STRING_AGG` or `FOR XML` instead. Also you should quote all table and column names with `quotename` instead of trying to do it yourself. You may also want to filter to `n/varchar` and `xml` columns only — Charlieface, Mar 24 '21 at 21:00
So what exactly didn't work? You've not quoted the column name prior to `from` so if any column exists with spaces etc it will break. I'd also recommend a top(1) otherwise you'll find all rows in a table that matches which won't tell you anything you don't already know, and specific columns matching the data type you're looking for. Basically though, I tested with some tables and known data, it works. — Stu, Mar 24 '21 at 21:06
I have used the approach in this [answer](https://stackoverflow.com/a/27361062/632537). — Isaac, Mar 24 '21 at 21:08
Does this answer your question? [Find a string by searching all tables in SQL Server Management Studio 2008](https://stackoverflow.com/questions/15757263/find-a-string-by-searching-all-tables-in-sql-server-management-studio-2008) — Dale K, Mar 24 '21 at 21:14
Actually I have tried many options which are listed in the - https://stackoverflow.com/questions/15757263/find-a-string-by-searching-all-tables-in-sql-server-management-studio-2008/27361062#27361062 - But none of them are returning response, even i waited for 15 minutes, still no response — Kumas, Mar 25 '21 at 13:09

score 0 · Answer 1 · answered Mar 26 '21 at 04:13

I made changes to the code you already had to include the XML datatype and cast the XML into NVARCHAR (MAX) in the WHERE clause and in the table variable.

USE DATABASE_NAME
DECLARE @SearchStr nvarchar(100) = 'SEARCH_TEXT'
DECLARE @Results TABLE (ColumnName nvarchar(370), ColumnValue nvarchar(max))

SET NOCOUNT ON

DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
SET  @TableName = ''
SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')

WHILE @TableName IS NOT NULL

BEGIN
    SET @ColumnName = ''
    SET @TableName = 
    (
        SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
        FROM     INFORMATION_SCHEMA.TABLES
        WHERE         TABLE_TYPE = 'BASE TABLE'
            AND    QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
            AND    OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA) + '.' +     
            QUOTENAME(TABLE_NAME) ), 'IsMSShipped' ) = 0 )

    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', 'int', 
                'decimal','xml') AND QUOTENAME(COLUMN_NAME) > @ColumnName )

    IF @ColumnName IS NOT NULL

    BEGIN
        INSERT INTO @Results
        EXEC
        (
            'SELECT ''' + @TableName + '.' + @ColumnName + ''','+ 
            'cast('+    @ColumnName+  ' as nvarchar(max))'
            +'FROM ' + @TableName + ' (NOLOCK) ' +
            ' WHERE cast( ' +  @ColumnName + ' as nvarchar(max)) LIKE ' + @SearchStr2
        )
    END
   END    
END
SELECT ColumnName, ColumnValue FROM @Results

Thanks for your time, I tried the above query and I waited almost 15 minutes, still no response/result so i have to cancel the query. — Kumas, Mar 26 '21 at 11:58

How do I find in which table Data has my given value in SQL

1 Answers1