Problem:

Sometime,  have any value, but we are unable to find, from where this value is displayed. to know what is table name and column name from where this value is beiing displayed.

we have written some ms sql script, that will take paramater as "KeyWords" or  Any data like name, or any integer and it will return the  table and column name from the selected database.

Search value in database and return table and column name

Solution:

 

DECLARE @SearchbyStringOrInt nvarchar(100) = 'ST'
DECLARE @RturnResul TABLE (ColumnName nvarchar(370), ColumnValue nvarchar(3630))

SET NOCOUNT ON

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

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

        IF @ColumnName IS NOT NULL

        BEGIN
            INSERT INTO @RturnResul
            EXEC
            (
                'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT((cast(' + @ColumnName + ' as nvarchar(max))), 3630) 
                FROM ' + @TableName + ' (NOLOCK) ' +
                ' WHERE (cast(' + @ColumnName + ' as nvarchar(max))) LIKE ' + @SearchbyStringOrInt2
            )
        END
    END    
END

SELECT ColumnName, ColumnValue FROM @RturnResul
 

Search value in ms sql server database 

Find table name and coumn name where value is being store.