Welcome To TechBrothersIT: How to find and Replace Text in all the columns in all the tables in SQL server Database

Scenario: Download Script

You are working as SQL Server / T-SQL developer for Auto Insurance Company. They have some bad data in there database. They would like you to write script that should be able to find the string in all the columns in all the tables in a database and replace with correct string.

For example, want you to find 'MyName' string in all the tables in a database and update to 'TechBrothersIT'. They want you to search this string in all columns of data type such as char, nchar, varchar, nvarchar, text and ntext.

Solution:

We can use cursor to find all the strings and then update to our required string in all the columns in all the tables in a database. Just few suggestions before you go ahead and run the script

1) Test the script on Dev database

2) Maybe you would like to just get the queries, so comment out the EXEC part for update. By doing that you will get the queries and then you can run on your convenience.

3) Take the backup of Production Database, restore in UAT. Run the script. Note the time and verify all the updates and ask the users to verify as well.

4) Schedule the time to run script on weekend or off hours as sometime databases are really big and can take long time to search and update value in each of the column for all the tables in a database.

here is the script.

USE YourDatabaseName
GO

Declare @SearchString VARCHAR(100)
Declare @UpdateTo VARCHAR(100)
--Provide the String here. 
--I am using MyName for search and updating to TechBrothersIT
SET @SearchString='MyName'
SET @UpdateTo='TechBrothersIT'
DECLARE @DatabaseName VARCHAR(100)
DECLARE @SchemaName VARCHAR(100)
DECLARE @TableName VARCHAR(100)
DECLARE @ColumnName VARCHAR(100)
DECLARE @FullyQualifiedTableName VARCHAR(500)
Declare @DataType VARCHAR(50)

--Create Temp Table to Save Results
IF OBJECT_ID('tempdb..#Results') IS NOT NULL
    DROP TABLE #Results

CREATE TABLE #Results (
     DatabaseName VARCHAR(100)
    ,SchemaName VARCHAR(100)
    ,TableName VARCHAR(100)
    ,ColumnName VARCHAR(100)
    ,ColumnDataType VARCHAR(50)
    ,TotalTableRowCount INT
    ,UpdatedRecordCount INT
    )

DECLARE Cur CURSOR
FOR
SELECT C.Table_CataLog
    ,C.Table_Schema
    ,C.Table_Name
    ,C.Column_Name
    ,'[' + C.Table_CataLog + ']' + '.[' + C.Table_Schema + '].' 
    + '[' + C.Table_Name + ']' AS FullQualifiedTableName,
    C.Data_Type
FROM information_schema.Columns C
INNER JOIN information_Schema.Tables T ON C.Table_Name = T.Table_Name
    AND T.Table_Type = 'BASE TABLE'
    and (C.Data_Type like '%CHAR%'
    or C.Data_Type like '%Text%')


OPEN Cur
FETCH NEXT
FROM Cur
INTO @DatabaseName
    ,@SchemaName
    ,@TableName
    ,@ColumnName
    ,@FullyQualifiedTableName
    ,@DataType

WHILE @@FETCH_STATUS = 0
BEGIN
--Handle Text or NText Columns
    
    DECLARE @ColumnName1 VARCHAR(50)
    IF @DataType like '%Text%'
    BEGIN 
    SET @ColumnName1='LTRIM(RTRIM(CAST('+@ColumnName + ' AS NVARCHAR(MAX))))'
    END 
    ELSE
    BEGIN 
    SET @ColumnName1=@ColumnName
    END


    DECLARE @SQL NVARCHAR(MAX) = NULL
    DECLARE @UpdateSQL NVARCHAR(MAX)
    SET @SQL = ' Select ''' + @DatabaseName + ''' AS DatabaseName, ''' 
    + @SchemaName + ''' AS TableName,
      ''' + @TableName + ''' AS SchemaName,
      ''' + @ColumnName + ''' AS ColumnName,
      ''' + @DataType + ''' AS ColumnName,
      (Select count(*) from ' + @FullyQualifiedTableName + ' With (Nolock)) 
      AS TotalTableRowCount,
      count(*) as UpdatedRecordCount from ' + @FullyQualifiedTableName 
      + 'With (Nolock) Where  '+@ColumnName+' like '''+'%'+ @SearchString+'%'''

      -- Print @SQL
    INSERT INTO #Results
    EXEC (@SQL)

      --Prepare and Execute Update Statement
      SET @UpdateSQL='Update '+@FullyQualifiedTableName+'
                     SET '+@ColumnName+'=Replace('+@ColumnName1+','''

+@SearchString+''','+''''+@UpdateTo+''')

Where  '+@ColumnName+' like '''+'%'+ @SearchString+'%'''
                     Print @UpdateSQL
                     EXEC(@UpdateSQL)
   

    FETCH NEXT
    FROM Cur
    INTO @DatabaseName
        ,@SchemaName
        ,@TableName
        ,@ColumnName
        ,@FullyQualifiedTableName
        ,@DataType
END
CLOSE Cur
DEALLOCATE Cur

SELECT *
from #Results
Where UpdatedRecordCount<>0
--drop table #Results

Once you run above query, you will get a nice table with Table Name, Column Name, Data type, Total Row Count in a table and Updated Row Count.

here is my output for above query when I executed to update 'MyName' to 'TechBrothersIT'

How to find and update string in all the columns in all the tables in SQL Server Database Dynamically

Check out our other posts on Data Analysis / Data Validation / Data Cleansing

3 comments:

dsadasdFebruary 27, 2024 at 9:22 AM
This comment has been removed by the author.
dsadasdFebruary 27, 2024 at 9:22 AM
This comment has been removed by the author.
dsadasdFebruary 27, 2024 at 9:23 AM
While learning about enclomiphene dosage, I stumbled across a resource that truly stands out for its in-depth analysis. The article, which you can find here https://valhallavitality.com/blog/reducing-food-noise-and-chatter-with-glp-1-therapy , provides a comprehensive breakdown of the subject matter, making it a valuable guide for anyone seeking to understand this topic. In particular, I found the comparison with moving prices midway through the article to be especially insightful. It's always refreshing to come across well-researched, clear, and precise information like this.

Welcome To TechBrothersIT

Label

How to find and Replace Text in all the columns in all the tables in SQL server Database - SQL Server / T-SQL Tutorial

Scenario: Download Script

Solution:

3 comments: