2

Goal

Locate a specific word among tables in a database and display the name of the table and column in a list.

Problem

There are TOO many tables to locate a specific word in the database.

Info

  • Using SQL Server 2012
  • Every table use a 'schemaname.tablename'
9

This will not be fast (this took over a minute to produce 0 results in my local copy of 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;

If you need to support sql_variant for some reason (I recommend against it), only a few changes are needed:

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;
1

Community wiki answer:

You could use the following script, which has been used to find bad data. It might be not fast, but it has worked for me on several occasions when I needed it:

/*
- Search through tables to find specific text
- Written by Luis Chiriff (with help from SQL Server Central)
- luis.chiriff@gmail.com @ 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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.