Solved

Help modifying search and replace stored proc

Posted on 2007-12-04
4
221 Views
Last Modified: 2008-02-01
Hi everyone.
I need to modify this search and replace stored proc currently If I perform

exec searchandreplace 'tom', 'tommy'   it will find things like 'tomorrow' and make it 'tommyorrow'. What I need it to do is only do replacements for Full String replacement ("tom" must match "tom" exactly)

A work around to having to find the Full String replacement ("tom" must match "tom" exactly) could be a way to specify a specific column names to replace in.

But for this to work It would have to work for a range of column names as its not exactly the same in all tables.

Thanks



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

 

--- disable triggers

/*To disable triggers*/

DECLARE C CURSOR FOR

SELECT 'ALTER TABLE ' + QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) + ' DISABLE TRIGGER ALL'

FROM INFORMATION_SCHEMA.TABLES

WHERE

OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)), 'IsMSShipped') = 0

AND TABLE_TYPE = 'BASE TABLE'

OPEN C

FETCH NEXT FROM C INTO @SQL

WHILE @@FETCH_STATUS = 0 BEGIN

  EXEC( @SQL )

  FETCH NEXT FROM C INTO @SQL

END

CLOSE C

DEALLOCATE C

 

--- original script begin

      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 C

                        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

--- begin test for computed

                              AND NOT EXISTS (SELECT * FROM SYSCOLUMNS S

                                WHERE S.NAME = C.COLUMN_NAME

                                  AND S.ID = OBJECT_ID(C.TABLE_NAME)

                                  AND S.ISCOMPUTED = 1)

--- end test for computed

                  )

      

                  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

 

--- ENABLE TRIGGERS

 

DECLARE C CURSOR FOR

SELECT 'ALTER TABLE ' + QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) + ' ENABLE TRIGGER ALL'

FROM INFORMATION_SCHEMA.TABLES

WHERE

OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)), 'IsMSShipped') = 0

AND TABLE_TYPE = 'BASE TABLE'

OPEN C

FETCH NEXT FROM C INTO @SQL

WHILE @@FETCH_STATUS = 0 BEGIN

  EXEC( @SQL )

  FETCH NEXT FROM C INTO @SQL

END

CLOSE C

DEALLOCATE C

Open in new window

0
Comment
Question by:tconnelly
  • 2
4 Comments
 
LVL 11

Accepted Solution

by:
Otana earned 500 total points
ID: 20409428
If you want an exact match, take the '%' out of your string.
0
 

Author Comment

by:tconnelly
ID: 20409492
so make the line

SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')

like this ?

SET @SearchStr2 = QUOTENAME(@SearchStr,'''')
0
 
LVL 11

Expert Comment

by:Otana
ID: 20409515
Yes, try it and see what happens.
0
 
LVL 25

Expert Comment

by:imitchie
ID: 20409681
line 114:       DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SQL  
line 36: -- delete this line
line 80:        ') WHERE ' + @ColumnName + ' = ' + @SearchStr

Hi tconnelly, this one is easy enough. The other aspects will be more tricky, but you'll get much more traction with others taking a task at a time. Have fun.
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now