Search All tables in database where a string matches and get table structure

Question

I have a string and I want to search that string in all the tables of Database.

Fortunately, I have the Query to search in all the tables of the database and get me the result which is quite impressive.

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

And I got the result

*The Problem: * I want the third column with this result and that is primary_key column

Basically I have to run the updation task on this result, so I want primary key for my where clause to apply on.

So please update this query and help me to get the third column (primary_key column) with this result

PS: if anyone has another solution to this problem most welcome

The basic problem in simple words is:

A search of a specific string in database tables and update the values of the column value where the specific string matches.

Where are you guys finding this ancient code? It's almost identical to the code in [this question](https://stackoverflow.com/questions/62015993/search-a-database-for-text-and-int-is-returning-error-the-multi-part-identifier) from two hours ago. — AlwaysLearning, May 26 '20 at 09:04
Yes Code might be the same, but the problem is different that I want to resolve with this — Dupinder Singh, May 26 '20 at 09:06
I removed the tag for Oracle's PL/SQL as this is clearly T-SQL for SQL Server — , May 26 '20 at 09:09
You can try to use the DataGrip to solve this task: https://www.jetbrains.com/help/datagrip/full-text-search-for-databases.html — Scrappy Coco, May 26 '20 at 09:48
You assume that every table has a primary key composed of a single column. Is that safe? But the logic can be easily extended by fetching the primary key column name (here is your assumption) when you establish the identity of the table in your loop. — SMor, May 26 '20 at 12:05
@SMor if you could help me to write the T-Sql that would be good, I am naive in T-SQL — Dupinder Singh, May 26 '20 at 12:19

score 1 · Accepted Answer · answered May 27 '20 at 05:04

Try this :)

USE springhibernate
DECLARE @SearchStr nvarchar(100) = 'James'
DECLARE @Results TABLE (ColumnName nvarchar(370), ColumnValue nvarchar(3630), PrimaryColumnName nvarchar(370))
SET NOCOUNT ON
DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @PrimaryColumnName 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
    )
    IF @TableName IS NOT NULL
    BEGIN
        SET @PrimaryColumnName = (
                SELECT  MIN(QUOTENAME(cd.TABLE_SCHEMA) + '.' + QUOTENAME(cd.TABLE_NAME) + '.' + QUOTENAME(cd.COLUMN_NAME))
                from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE cd
        join INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc ON tc.CONSTRAINT_NAME = cd.CONSTRAINT_NAME
                WHERE
                QUOTENAME(cd.TABLE_SCHEMA) + '.' + QUOTENAME(cd.TABLE_NAME) = @TableName

                AND tc.CONSTRAINT_TYPE = 'PRIMARY KEY'
            )
    END
    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
            IF @PrimaryColumnName IS NOT NULL
            BEGIN
                INSERT INTO @Results
                EXEC
                (
                    'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630), LEFT(' + @PrimaryColumnName + ', 3630)
                    FROM ' + @TableName + ' (NOLOCK) ' +
                    ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
                )
            END
            ELSE
            BEGIN
                INSERT INTO @Results
                EXEC
                (
                    'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630), NULL   
                    FROM ' + @TableName + ' (NOLOCK) ' +
                    ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
                )
            END
        END
    END    
END
SELECT * FROM @Results

Thank you, You just same my alot of time. – Dupinder Singh May 27 '20 at 05:26 — Dupinder Singh, May 27 '20 at 05:26
Welcome, always happy to help with SQL – Tarun Chopra Jun 05 '20 at 12:44 — Tarun Chopra, Jun 05 '20 at 12:44

score 1 · Answer 2 · answered May 27 '20 at 07:46

I want to fetch the primaryKey column also so that I can run following Query

UPDATE <TABLE_NAME> SET <COLUMN_NAME> = REPLACE(<COLUMN_NAME>, '<REPLACE_STRING>', '') where <PRIMARY_KEY_COLUMN_NAME> = PRIMARY_KEY;

So @Tarun Query help me to get * TABLE_NAME * COLUMN_NAME * PRIMARY_KEY But I need one more data PRIMARY_KEY_COLUMN_NAME So I updated little bit

DECLARE @SearchStr nvarchar(100) = '%JAMES%'
DECLARE @Results TABLE (ColumnName nvarchar(370), ColumnValue nvarchar(3630), PrimaryKeyId nvarchar(370), PrimaryColumnName nvarchar(370))
SET NOCOUNT ON
DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @PrimaryKeyId nvarchar(128),@PrimaryColumnName 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
    )
    IF @TableName IS NOT NULL
    BEGIN
        SET @PrimaryColumnName = (
                SELECT  MIN(QUOTENAME(cd.TABLE_SCHEMA) + '.' + QUOTENAME(cd.TABLE_NAME) + '.' + QUOTENAME(cd.COLUMN_NAME))
                from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE cd
        join INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc ON tc.CONSTRAINT_NAME = cd.CONSTRAINT_NAME
                WHERE
                QUOTENAME(cd.TABLE_SCHEMA) + '.' + QUOTENAME(cd.TABLE_NAME) = @TableName
                AND tc.CONSTRAINT_TYPE = 'PRIMARY KEY'
            )
    END
    BEGIN
        SET @PrimaryKeyId = (
                SELECT  MIN(QUOTENAME(cd.TABLE_SCHEMA) + '.' + QUOTENAME(cd.TABLE_NAME) + '.' + QUOTENAME(cd.COLUMN_NAME))
                from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE cd
        join INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc ON tc.CONSTRAINT_NAME = cd.CONSTRAINT_NAME
                WHERE
                QUOTENAME(cd.TABLE_SCHEMA) + '.' + QUOTENAME(cd.TABLE_NAME) = @TableName

                AND tc.CONSTRAINT_TYPE = 'PRIMARY KEY'
            )
    END
    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
            IF @PrimaryKeyId IS NOT NULL
            BEGIN
                INSERT INTO @Results
                EXEC
                (
                    'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630), LEFT(' + @PrimaryKeyId + ', 3630), ''' + @PrimaryColumnName +'''
                    FROM ' + @TableName + ' (NOLOCK) ' +
                    ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
                )
            END
            ELSE
            BEGIN
                INSERT INTO @Results
                EXEC
                (
                    'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630), NULL, ''' + @PrimaryColumnName +'''   
                    FROM ' + @TableName + ' (NOLOCK) ' +
                    ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
                )
            END
        END
    END    
END
SELECT * FROM @Results

And this is able to get

ColumnName | ColumnValue | PrimaryKeyId | PrimaryColumnName

Search All tables in database where a string matches and get table structure

2 Answers2