Search for a specific string into complete database tables in sql server

I usually do this when i have to find a table inside a big database and to find that i only have some data value for a guess.

To find a specific string into complete database there is no specific UI feature available in sql server client, But we could achieve this with a query. The query is little complicated for a newbie.

You just need to replace the DATABASE_NAME with your database name and 'SEARCH_TEXT' with your search text

    
    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