• How to replace-text-in-sql-databases-tables ?

    This article is very important ,as it will help you remove a hacked text in your Microsoft SQL database tables.

    First,we need to create the following stored procedure:
    =========================================================
    CREATE PROC SearchAndReplace
    (
    @SearchStr nvarchar(100),
    @ReplaceStr nvarchar(100)
    )
    AS
    BEGIN

    — Copyright © 2002 Narayana Vyas Kondreddi. All rights reserved.
    — Purpose: To search all columns of all tables for a given search string and replace it with another string
    — Written by: Narayana Vyas Kondreddi
    — Site: http://vyaskn.tripod.com
    — Tested on: SQL Server 7.0 and SQL Server 2000
    — Date modified: 2nd November 2002 13:50 GMT

    SET NOCOUNT ON

    DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110), @SQL nvarchar(4000), @RCTR int
    SET  @TableName = ”
    SET @SearchStr2 = QUOTENAME(‘%’ + @SearchStr + ‘%’,””)
    SET @RCTR = 0

    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
    SET @SQL=    ‘UPDATE ‘ + @TableName +
    ‘ SET ‘ + @ColumnName
    + ‘ =  REPLACE(‘ + @ColumnName + ‘, ‘
    + QUOTENAME(@SearchStr, ””) + ‘, ‘ + QUOTENAME(@ReplaceStr, ””) +
    ‘) WHERE ‘ + @ColumnName + ‘ LIKE ‘ + @SearchStr2
    EXEC (@SQL)
    SET @RCTR = @RCTR + @@ROWCOUNT
    END
    END
    END

    SELECT ‘Replaced ‘ + CAST(@RCTR AS varchar) + ‘ occurence(s)’ AS ‘Outcome’
    END
    GO
    ==========================================================
    After That ,run the following command in SQL screen to replace any text in Database tables:

    exec SearchAndReplace ‘The_text_you_want_to_replace’,’The_text_you_want_to_replace_with’ ;

    Now you got text replaced with the one you typed 🙂

    Categories: MSSQL Server

    Comments are currently closed.