Need script for Search and Update in all columns in all tables using keyword

  • Hello All,

    What is basically what the subject says, I want to be able to run a script that will REPLACE the word "Temp Manhattan Mortgage" to "Temp Home Finance" in all columns in all tables.

    thanks in advance!

     

     

  • Do a search in the script section here. Very likely you'll find there what you are looking for.

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • I found this a while ago and only tested it once.  Good Luck:

    CREATE PROC SearchAndReplace

    (

     @SearchStr nvarchar(100),

     @ReplaceStr nvarchar(100)

    )

    AS

    BEGIN

    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

     &nbsp

      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

      &nbsp

     

       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

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply