Searching for text over multiple columns?

Question

Our company is using SQL Server 2008 to manage the website's database. We have currently had a name change and so I need to change all occurrences of our old company name.

The database has multiple Tables and A LOT of columns under each table, I am only interested in finding and updating the text from all columns in one table.

Essentially what I need to be able to do is; find the string "CSQTC" over all columns in a table named "Practices", note that some columns may not contain strings, and some values in the columns may be null.

I think I know how to search for text over multiple columns, but it is a lot of code and I am sure there is a better way. This is how I think I do it

WHERE    columnName LIKE '%CSQTC%'
         AND columnName2 LIKE '%CSQTC%'
         AND columnName3 LIKE '%CSQTC%'
         AND ....

Surely there is a better way?

Thanks heaps!

EDIT: I forgot to ask how I can replace each occurence of 'CSQTC' to be 'GPTQ' instead? Thanks again

Answer 1

You could probably write a stored procedure that would

1. look for all columns containing text in your table (I don't know sql server especially, but with MySQL you'd look in the information_schema)
2. for each matching column, do an update request to replace the string you want.

And then obviously call that procedure (and maybe discard it unless you think you'll need it later on).

For the replacement part, it will be a simple use of REPLACE(columnName, 'CSQTC', 'GPTQ') (see REPLACE documentation at Microsoft's Technet)

P.S. see SQL server query to get the list of columns in a table along with Data types, NOT NULL, and PRIMARY KEY constraints for how to get columns of a table, and SQL Server stored procedure beginner's guide [closed] for stored procedures on sql server.

Answer 2

You can start with this:

SELECT  c.name ,
        t.name
FROM    sysobjects o
        INNER JOIN syscolumns c ON c.id = o.id
        INNER JOIN systypes t ON t.xusertype = c.xusertype
WHERE   o.name = 'YourTableName'
        AND t.name IN ( 'varchar', 'nvarchar', 'text', 'ntext', 'char', 'nchar' )

To get all the columns that have text from the table.

Also you can do this:

SELECT  'Update ' + QUOTENAME(o.name) + ' Set ' + c.name + ' = Replace(' + QUOTENAME(c.name) + ', ''CSQTC'', ''GPTQ'')'
        + ' Where ' + QUOTENAME(c.name) + ' LIKE ''%CSQTC%'''
FROM    sysobjects o
        INNER JOIN syscolumns c ON c.id = o.id
        INNER JOIN systypes t ON t.xusertype = c.xusertype
WHERE   o.name = 'YourTableName'
        AND t.name IN ( 'varchar', 'nvarchar', 'text', 'ntext', 'char', 'nchar' )

To get the Update instructions for each column of the table.