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.

Solution 1

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');

Solution 2

Using queries for this will make this more complex than really needed. Why not consider some of the free SQL Search tools that exist out there. ApexSQL has ApexSQL Search, and there is also SQL Search from Red-Gate. Both of these will get the job done easily.

Solution 3

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.

Solution 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'); 

View more solutions

Comments

[edosoft]

Nice script. However it seems to me this only updates a single table (called 'MyTable') ?

[marc_s]

Avoid cursors whenever possible! They're evil, they're procedural and don't fit SQL Server well. The other answer is the much better answer because of that fact.

[Eric]

marc, did you read my bloody post? Since we're only dealing with a few columns, cursor's are up for this task. The other answers builds a bunch of statements that would have to be copied then run by hand, and in fact, is contained within this script, which is plug-and-play. If you read the post, I acknowledged the danger of cursors when dealing with millions (or even thousands!) of rows of data. Cursors are not always bad, and opinions like that will only keep you from using the most effective tool to do a job.

[Andomar]

+1 Maybe add (NOLOCK) or "set transaction isolation level read uncommitted" if you plan to run this on a production database

[edosoft]

what would adding NOLOCK accomplish? I would think sys.objects isn't updated that often.

[Levitikon]

After running this and then running the results, I get: Msg 102, Level 15, State 1, Line 138 Incorrect syntax near 'union'.