SQLTeam.com | Weblogs | Forums

Search a string in a database

Hello,
I wrote this query for searching a string in whole database . Earlier it was working properly now it doesn't fetch full result at all . I don't get what is wrong with this query . Please help

QUERY

    /*
    - 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 = 'abcdef'
    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

this is a very very very very very common scenario

one idea ..
how to find out what's not working

Lets say you have a script
1
2
3
4

First run 1 .. ok
Then 1 , 2 ... ok
Then 1,2,3 .. NOT OK means some issue in 3
Find out whats wrong in 3 ..
Now 1,2,3 ok
then 1,2,3,4 ... if this is also OK

Now Script is Fixed


There are a lot of other scenario's ALSO ..possibilities
PAIN in the ***** to find out
As usual ..

I mean have to check each and everything MANUALLY

Is this for mysql or Microsoft sql server technology?

Hello yosiasz, This query is for sql server

I found that 'tempcount' doesn't exists in sys.objects

Nice Saha123

HopeFully that resolves your issue .. :+1:

This is very hard to read and looks like it will be a maintenance nightmare. At the end what would the final result look like to whoever is using this search query. Best to avoid cursor loops as well