dfsafds

gistfile1.txt

DECLARE
   @SearchText varchar(200),
   @Table varchar(100),
   @TableID int,
   @ColumnName varchar(100),
   @String varchar(1000);
--modify the variable, specify the text to search for 
SET @SearchText = '%Per6%';
DECLARE CursorSearch CURSOR
    FOR SELECT name, object_id
        FROM sys.objects
      WHERE type = 'U';
--list of tables in the current database. Type = 'U' = tables(user-defined) 
OPEN CursorSearch;
FETCH NEXT FROM CursorSearch INTO @Table, @TableID;
WHILE
       @@FETCH_STATUS
       =
       0
    BEGIN
        DECLARE CursorColumns CURSOR
            FOR SELECT name
                  FROM sys.columns
                WHERE
                     object_id
                   =
                 @TableID AND system_type_id IN(167, 175, 231, 239);
        -- the columns that can contain textual data        
--167 = varchar; 175 = char; 231 = nvarchar; 239 = nchar        
OPEN CursorColumns;
        FETCH NEXT FROM CursorColumns INTO @ColumnName;
        WHILE
               @@FETCH_STATUS
               =
               0
            BEGIN
                SET @String = 'IF EXISTS (SELECT * FROM '
                            + @Table
                            + ' WHERE '
                            + @ColumnName
                            + ' LIKE ''%'
                            + @SearchText
                            + ''') PRINT '''
                            + @Table
                            + ', '
                            + @ColumnName
                            + '''';
                EXECUTE (@String);
                FETCH NEXT FROM CursorColumns INTO @ColumnName;
            END;
        CLOSE CursorColumns;
        DEALLOCATE CursorColumns;
        FETCH NEXT FROM CursorSearch INTO @Table, @TableID;
    END;
CLOSE CursorSearch;
DEALLOCATE CursorSearch;