Localize uma palavra específica em um banco de dados

Isso não será rápido (levou mais de um minuto para produzir 0 resultados em minha cópia local do AdventureWorks).

DECLARE @SpecificWord NVARCHAR(32);
SET @SpecificWord = N'donut';


DECLARE @sql NVARCHAR(MAX);
SET @sql = N'SELECT NULL,NULL WHERE 1 = 0';
SET @SpecificWord = N'%' + @SpecificWord + N'%';

SELECT @sql += N' UNION ALL SELECT '''
 + QUOTENAME(s.name) + N'.' + QUOTENAME(t.name)
 + ''',''' + QUOTENAME(c.name) + ''' WHERE EXISTS
 (
   SELECT 1 FROM ' + QUOTENAME(s.name) + N'.' + QUOTENAME(t.name)
     + N' WHERE ' + QUOTENAME(c.name) + N' LIKE @s
 )'
FROM sys.columns AS c
INNER JOIN sys.tables AS t
ON c.[object_id] = t.[object_id]
INNER JOIN sys.schemas AS s
ON t.[schema_id] = s.[schema_id]
WHERE c.system_type_id IN (35,99,167,175,231,239);

EXEC sys.sp_executesql @sql, N'@s NVARCHAR(32)', @SpecificWord;

Se você precisar apoiar sql_variantpor algum motivo (recomendo contra isso), apenas algumas alterações são necessárias:

DECLARE @SpecificWord NVARCHAR(32);
SET @SpecificWord = N'donut';


DECLARE @sql NVARCHAR(MAX);
SET @sql = N'SELECT NULL,NULL WHERE 1 = 0';
SET @SpecificWord = N'%' + @SpecificWord + N'%';

SELECT @sql += N' UNION ALL SELECT '''
 + QUOTENAME(s.name) + N'.' + QUOTENAME(t.name)
 + ''',''' + QUOTENAME(c.name) + ''' WHERE EXISTS
 (SELECT 1 FROM ' + QUOTENAME(s.name) + N'.' 
 + QUOTENAME(t.name) + N' WHERE CONVERT(
   NVARCHAR(MAX), ' + QUOTENAME(c.name) 
 + N') LIKE @s)'
FROM sys.columns AS c
INNER JOIN sys.tables AS t
ON c.[object_id] = t.[object_id]
INNER JOIN sys.schemas AS s
ON t.[schema_id] = s.[schema_id]
WHERE c.system_type_id IN (35,98,99,167,175,231,239);

EXEC sys.sp_executesql @sql, N'@s NVARCHAR(32)', @SpecificWord;

_{Resposta do wiki da comunidade :}

Você pode usar o script a seguir, que foi usado para localizar dados incorretos. Pode não ser rápido, mas funcionou para mim em várias ocasiões quando precisei:

/*
- Search through tables to find specific text
- Written by Luis Chiriff (with help from SQL Server Central)
- [email protected] @ 24/11/2008 @ 11:54
*/

-- Variable Declaration

Declare @StringToFind VARCHAR(200), @Schema sysname, @Table sysname, @FullTable int, @NewMinID int, @NewMaxID int,
@SQLCommand VARCHAR(8000), @BaseSQLCommand varchar(8000), @Where VARCHAR(8000), @CountCheck varchar(8000) , @FieldTypes varchar(8000),
@cursor VARCHAR(8000), @columnName sysname, @SCn int, @SCm int
Declare @TableList table (Id int identity(1,1) not null, tablename varchar(250))
Declare @SQLCmds table (id int identity(1,1) not null, sqlcmd varchar(8000))
Declare @DataFoundInTables table (id int identity(1,1) not null, sqlcmd varchar(8000))


-- Settings

SET @StringToFind = 'territory'
SET NOCOUNT ON
SET @StringToFind = '%'+@StringToFind+'%'

-- Gathering Info

if ((select count(*) from sysobjects where name = 'tempcount') > 0)
    drop table tempcount

create table tempcount (rowsfound int)
insert into tempcount select 0

    -- This section here is to accomodate the user defined datatypes, if they have
    -- a SQL Collation then they are assumed to have text in them.
    SET @FieldTypes = ''
    select @FieldTypes = @FieldTypes + '''' + rtrim(ltrim(name))+''',' from systypes where collation is not null or xtype = 36
    select @FieldTypes = left(@FieldTypes,(len(@FieldTypes)-1))

insert into @TableList (tablename) 
    select name from sysobjects 
    where xtype = 'U' and name not like 'dtproperties' 
    order by name

-- Start Processing Table List

select @NewMinID = min(id), @NewMaxID = max(id) from @TableList

while(@NewMinID <= @NewMaxID)
    Begin

    SELECT @Table = tablename, @Schema='dbo', @Where = '' from @TableList where id = @NewMinID

    SET @SQLCommand = 'SELECT * FROM ' + @Table + ' WHERE' 
    -- removed ' + @Schema + '.

    SET @cursor = 'DECLARE col_cursor CURSOR FOR SELECT COLUMN_NAME
    FROM [' + DB_NAME() + '].INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_SCHEMA = ''' + @Schema + '''
    AND TABLE_NAME = ''' + @Table + '''
    AND DATA_TYPE IN ('+@FieldTypes+')'
    --Original Check, however the above implements user defined data types --AND DATA_TYPE IN (''char'',''nchar'',''ntext'',''nvarchar'',''text'',''varchar'')'

    EXEC (@cursor)

    SET @FullTable = 0
    DELETE FROM @SQLCmds

    OPEN col_cursor   
    FETCH NEXT FROM col_cursor INTO @columnName   

    WHILE @@FETCH_STATUS = 0   
    BEGIN   

        SET @Where = @Where + ' [' + @columnName + '] LIKE ''' + @StringToFind + ''''
        SET @Where = @Where + ' OR'

        --PRINT @Table + '|'+ cast(len(isnull(@Where,''))+len(isnull(@SQLCommand,'')) as varchar(10))+'|'+@Where

        if (len(isnull(@Where,''))+len(isnull(@SQLCommand,'')) > 3600)
            Begin
                SELECT @Where = substring(@Where,1,len(@Where)-3)
                insert into @SQLCmds (sqlcmd) select @Where
                SET @Where = ''
            End

        FETCH NEXT FROM col_cursor INTO @columnName   
    END   

    CLOSE col_cursor   
    DEALLOCATE col_cursor 

    if (@Where <> '')
        Begin
            SELECT @Where = substring(@Where,1,len(@Where)-3)
            insert into @SQLCmds (sqlcmd) 
                select @Where --select @Table,count(*) from @SQLCmds
        End

    SET @BaseSQLCommand = @SQLCommand

    select @SCn = min(id), @SCm = max(id) from @SQLCmds
    while(@SCn <= @SCm)
        Begin

        select @Where = sqlcmd from @SQLCmds where ID = @SCn

        if (@Where <> '')
            Begin

            SET @SQLCommand = @BaseSQLCommand + @Where
            SELECT @CountCheck = 'update tempcount set rowsfound = (select count(*) '+ substring(@SQLCommand,10,len(@SQLCommand)) + ')'
            EXEC (@CountCheck) 

            if ((select rowsfound from tempcount) > 0)
                    Begin
                        PRINT '--- ['+cast(@NewMinID as varchar(15))+'/'+cast(@NewMaxID as varchar(15))+'] '+@Table + ' ----------------------------------[FOUND!]'
                        --PRINT '--- [FOUND USING:] ' +@SQLCommand
                        insert into @DataFoundInTables (sqlcmd) select @SQLCommand
                        EXEC (@SQLCommand) 
                        update tempcount set rowsfound = 0
                    End
            else
                    Begin
                        PRINT '--- ['+cast(@NewMinID as varchar(15))+'/'+cast(@NewMaxID as varchar(15))+'] '+@Table
                    End
            End

        SET @SCn = @SCn + 1
        End

    set @NewMinID = @NewMinID + 1
    end

if ((select count(*) from sysobjects where name = 'tempcount') > 0)
    drop table tempcount

/*

This will now return all the sql commands you need to use

*/


select @NewMinID = min(id), @NewMaxID = max(id) from @DataFoundInTables

if (@NewMaxID > 0)
    Begin
    PRINT ' '   
    PRINT ' '   
    PRINT '-----------------------------------------'
    PRINT '----------- TABLES WITH DATA ------------'
    PRINT '-----------------------------------------'
    PRINT ' '   
    PRINT 'We found ' + cast(@NewMaxID as varchar(10)) + ' table(s) with the string '+@StringToFind
    PRINT ' '

    while(@NewMinID <= @NewMaxID)
        Begin

        select @SQLCommand = sqlcmd from @DataFoundInTables where ID = @NewMinID

        PRINT @SQLCommand

        SET @NewMinID = @NewMinID + 1
        End

    PRINT ' '   
    PRINT '-----------------------------------------'

    End