SQL: search for a string in every varchar column in a database

Question

I have a database where a misspelled string appears in various places in different tables. Is there a SQL query that I can use to search for this string in every possible varchar/text column in the database?

I was thinking of trying to use the information_schema views somehow to create dynamic queries, but I'm not sure if that will work, or if there's a better way.

I'm using MS SQL Server if that helps.

Answer 1

You could use a cursor and the sys.tables/sys.columns views to go through them. Give me a minute, and I'll give you the code.

Update: Here you are:

declare @col_name nvarchar(50)
declare @sql nvarchar(max)
declare @tbl_name nvarchar(50)
declare @old_str nvarchar(50)
declare @new_str nvarchar(50)

set @old_str = 'stakoverflow'
set @new_str = 'StackOverflow'

declare fetch_name cursor for
select 
    c.name,
    t.name
from 
    sys.columns c
    inner join sys.tables t on c.object_id = t.object_id
    inner join sys.types y on c.system_type_id = y.system_type_id
where
    y.name like '%varchar'
    or y.name like '%text'

open fetch_name

fetch next from fetch_name into @col_name, @tbl_name

while @@fetch_status = 0
begin
    set @sql = 'UPDATE ' + @tbl_name + ' SET ' + 
     @col_name + ' = replace(' + 
      @col_name + ',''' + 
      @old_str + ''',''' + 
      @new_str + ''')'

    exec sp_executesql @sql

    fetch next from fetch_name into @col_name
end

close fetch_name
deallocate fetch_name

This will get you everything you need. It grabs the columns that are varchar, nvarchar, text, and ntext from your database, cycle through the columns and update each one.

Of course, you could also do this to create a concatenated SQL statement and do one big update at the end, but hey, that's your preference.

And for the record, I don't like cursors, but since we're dealing with a few columns and not millions of rows, I'm okay with this one.

Answer 2

Using the technique found here the following script generates SELECT's for all ((n)var)char columns in the given database. Copy/paste the output, remove the very last 'union' and execute.. You'll need to replace MISSPELLING HERE with the string you're looking for.

select 
'select distinct ''' + tab.name + '.' + col.name 
+ '''  from [' + tab.name 
+ '] where [' + col.name + '] like ''%MISSPELLING HERE%'' union ' 
from sys.tables tab 
join sys.columns col on (tab.object_id = col.object_id)
join sys.types types on (col.system_type_id = types.system_type_id) 
where tab.type_desc ='USER_TABLE' 
and types.name IN ('CHAR', 'NCHAR', 'VARCHAR', 'NVARCHAR');

Answer 3

the comment the cursors are evil is uninformed. Cursors have their place and actually seems like a good use of a cursor.

Answer 4

SQL Server 2000 version of the script above (from edosoft):

select  
'select distinct ''[' + tab.name + ']'' as TableName, ''[' + col.name + ']'' as ColumnName'
+ ' from [' + users.name + '].[' + tab.name  
+ '] where UPPER([' + col.name + ']) like ''%MISSPELLING HERE%'' union '  
from sysobjects tab  
join syscolumns col on (tab.id = col.id) 
join systypes types on (col.xtype = types.xtype)  
join sysusers users on (tab.uid = users.uid)
where tab.xtype ='U'  
and types.name IN ('char', 'nchar', 'varchar', 'nvarchar');