Post reply

Search entire DB for a string

  • September 23, 2010 at 5:18 am

    #157217

    Hi,

    Anyone have a script/proc for this to run on 2k5?

    Thanks,

    BLL

  • September 23, 2010 at 5:30 am

    #1226226

    Can you be more specific about what you want to do ?

  • September 23, 2010 at 5:54 am

    #1226233

    To search the data, in a database, for the existence of a string of data.

  • September 23, 2010 at 6:14 am

    #1226247

  • September 23, 2010 at 6:45 am

    #1226278

    Well as far as i know there is no way off doing this. A maybe acceptable solution would be to search all "normal" string column (char/varchar and unicode version).

    However doing this on a large database would take ALOT of time since each search would be a full table scan... and there will be ALOT off those.

    Now the code below could require modifications depending on collation and/or the string being searched for. But its place to start.

    It returns the table/column and the data that was found. So no primary key of the row that it finds so you will need to make some individual selects to get that data as well. But it points you in the right direction... hopefully.

    USE AT YOUR OWN RISK

    --The string to search for

    declare @STR varchar(128)

    set @STR = 'hi'

    /*The searchcode*/

    declare @sql varchar(1024)

    declare @i int

    --Table for results

    create table #SearchRes (i integer identity, TableName varchar(128), ColumnName varchar(128), Value varchar(max) primary key (TableName, ColumnName, i))

    --Get all user defined tables and all columns of type CHAR/VARCHAR/NCHAR/NVARCHAR

    --WILL NOT WORK ON TEXT/XML/BINARY type of columns

    select Identity(int) i, so.Name TableName, sc.name ColumnName

    into #TableCol

    from SysObjects so

    join syscolumns sc on sc.id = so.id and sc.xtype in (167, 175, 231, 239)

    where so.xtype = 'U'

    order by so.Name, sc.Name

    --Loop until you puke (or the server crashes)

    set @i = 1

    while exists (select * from #TableCol where i > @i)

    begin

    select @sql = 'insert into #SearchRes (TableName, ColumnName, Value) select ''' + TableName + ''', ''' + ColumnName + ''', '+ ColumnName + ' from ' + TableName + ' where ' + ColumnName + ' like ''%' + @STR + '%''' from #TableCol where i = @i

    exec (@sql)

    set @i = @i + 1

    end

    select * from #SearchRes

    drop table #TableCol

    drop table #SearchRes

    /T

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply