So, have you ever had to replace a piece of text wherever it occured inside a SQL Server Database (without knowing where to even look)?  This can be a time consuming task, especially if you have a database with hundreds of tables and columns.

I had to perform this recently when a server moved to a new domain (changing all usernames etc).

Here is how you can make a quick start.  What this article will explain is how to generate the script which you can then use to perform the alteration (although of course you will need to review the resultant script, change it, test it and run at your own risk!)

First, open SQL Server Management Studio and connect to your database, creating a blank query window.  You will need to also click on the “Results to Text” button, like shown below.

Results to text

Now copy and paste the below script into your new query window.  This script will perform the script generation, replacing all instances of ‘ABC’ with ‘XYZ’.  Here is where you change what string you are wanting to replace with its new value.

    SET NOCOUNT ON
    DECLARE @SearchStr varchar(100)
    DECLARE @ReplaceStr varchar(100)
    SET @SearchStr = 'ABC'
    SET @ReplaceStr = 'XYZ'

    DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @Counter int, @Exists int

    SET @TableName = ''
    SET @Counter = 0

    CREATE TABLE #StringFound
    (
     occurances varchar(1000),
     tableName varchar(1000),
     columnName varchar(1000)
    )

    WHILE @TableName IS NOT NULL
    BEGIN
        SET @ColumnName = ''
        SET @TableName =
        (
            SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
            FROM     INFORMATION_SCHEMA.TABLES
            WHERE         TABLE_TYPE = 'BASE TABLE'
                AND    QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
                AND    OBJECTPROPERTY(
                        OBJECT_ID(
                            QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
                             ), 'IsMSShipped'
                               ) = 0
        )

        WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
        BEGIN
            SET @ColumnName =
            (
                SELECT MIN(QUOTENAME(COLUMN_NAME))
                FROM     INFORMATION_SCHEMA.COLUMNS
                WHERE         TABLE_SCHEMA    = PARSENAME(@TableName, 2)
                    AND    TABLE_NAME    = PARSENAME(@TableName, 1)
                    AND    DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
                    AND    QUOTENAME(COLUMN_NAME) > @ColumnName
            )

            IF @ColumnName IS NOT NULL
            BEGIN

                INSERT INTO #StringFound EXEC ('SELECT COUNT(*), ''' + @TableName + ''', ''' + @ColumnName + '''  FROM ' + @TableName + ' WHERE ' + @ColumnName + ' LIKE ''%' + @SearchStr + '%'' HAVING COUNT(*) > 0')
                SET @Counter = @Counter + @@ROWCOUNT

            END
        END
    END

    SELECT 'EXEC sp_MSforeachtable ''ALTER TABLE ? DISABLE TRIGGER ALL'''

    SELECT 'UPDATE [' + DB_NAME() + '].' + tableName + CHAR(13) + CHAR(10) +
           'SET ' + columnName + ' = REPLACE(' + columnName + ', ''' + @SearchStr + ''', ''' + @ReplaceStr + ''')' + CHAR(13) + CHAR(10) +
           'GO' + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10)
    FROM #StringFound
    ORDER BY tableName, columnName

    SELECT 'EXEC sp_MSforeachtable ''ALTER TABLE ? ENABLE TRIGGER ALL'''

    DROP TABLE #StringFound

    --PRINT CAST(@Counter AS varchar) + ' occurence(s)'

    SET NOCOUNT OFF

Simply execute the query and view the results.  Here is an example of the output script:

----------------------------------------------------------
EXEC sp_MSforeachtable 'ALTER TABLE ? DISABLE TRIGGER ALL'

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
UPDATE [TEMP_DATABASE].[dbo].[SIMPLE_NAME]
SET [FIRST_NAME] = REPLACE([FIRST_NAME], 'ABC', 'XYZ')
GO

UPDATE [TEMP_DATABASE].[dbo].[SIMPLE_NAME]
SET [LAST_NAME] = REPLACE([LAST_NAME], 'ABC', 'XYZ')
GO

UPDATE [TEMP_DATABASE].[dbo].[SIMPLE_NAME]
SET [USERNAME] = REPLACE([USERNAME], 'ABC', 'XYZ')
GO

---------------------------------------------------------
EXEC sp_MSforeachtable 'ALTER TABLE ? ENABLE TRIGGER ALL'

As stated previously, never run this straight onto your database without testing and verifying the results.  You will also probably want to fine tune the script so that its updates are more precise (adding in WHERE clauses to keep the changes more precise).  However, this should give you a good place to start and see what tables and columns you need to look for!

Hope this helps,

Stefan.

For more Web Code, ASP.NET, SQL Server and other development tips, please check back here at webcodeblog.com often.