DECLARE
    @search_string  VARCHAR(100),
    @TABLE_NAME     SYSNAME,
    @table_id       INT,
    @column_name    SYSNAME,
    @sql_string     VARCHAR(2000)
 
SET @search_string = 'deneme12345'
 
DECLARE tables_cur CURSOR FOR SELECT name, object_id FROM sys.objects WHERE TYPE = 'U'
OPEN tables_cur
FETCH NEXT FROM tables_cur INTO @TABLE_NAME, @table_id
 
WHILE (@@FETCH_STATUS = 0)
BEGIN
    DECLARE columns_cur CURSOR FOR SELECT name FROM sys.columns WHERE object_id = @table_id AND system_type_id IN (167, 175, 231, 239)
    OPEN columns_cur
 
    FETCH NEXT FROM columns_cur INTO @column_name
    WHILE (@@FETCH_STATUS = 0)
    BEGIN
        SET @sql_string = 'IF EXISTS (SELECT * FROM ' + @TABLE_NAME + ' WHERE [' + @column_name + '] LIKE ''%' + @search_string + '%'') PRINT ''' + @TABLE_NAME + ', ' + @column_name + ''''
 
        EXECUTE(@sql_string)
 
        FETCH NEXT FROM columns_cur INTO @column_name
    END
 
    CLOSE columns_cur
    DEALLOCATE columns_cur
 
    FETCH NEXT FROM tables_cur INTO @TABLE_NAME, @table_id
END
 
CLOSE tables_cur
DEALLOCATE tables_cur
 
 
 
 
 
-------------------------
 
Find a specified string in a database with all tables :
 
 
CREATE PROC SearchAllTables ( @SearchStr nvarchar(100) ) AS BEGIN

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')
                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
END 
 
 
 
------------------- 
 
 exec SearchAllTables 'deneme12345';
 
-------------------