Solved

Help modifying a SQL find and replace Stored procedure

Posted on 2007-12-03
11
518 Views
Last Modified: 2012-05-05
Hi experts,

I have this script that I have had for a few years that creates a DB find and replace stored proc. Although it has worked well in the passed I would like to modify this script to do the following.

A.      at the start disable all triggers on the DB it is running on and enable them at the completion
B.      I would like this script to not try to update any computed columns

I am more of a systems guy and was hoping one of you MS SQL experts might be able to help. I have pasted the Code below

Thanks

TC

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
            )

            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
0
Comment
Question by:tconnelly
  • 5
  • 5
11 Comments
 
LVL 25

Expert Comment

by:imitchie
ID: 20394371

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
 
LVL 6

Expert Comment

by:Rajesh_mj
ID: 20395394
Based On imitchie script,

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

 

 

--- 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

            )

 

            IF (@TableName IS NOT NULL)

	    BEGIN

		   -- Disable trigger

		    SELECT  @SQL = 'ALTER TABLE ' +@TableName+' DISABLE TRIGGER ALL'

		    EXEC   (@SQL)

		    WHILE (@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 

		   -- Enable trigger

		    SELECT  @SQL = 'ALTER TABLE ' +@TableName+' ENABLE TRIGGER ALL'

		    EXEC   (@SQL)	

	    END	

     

      END

 

      SELECT 'Replaced ' + CAST(@RCTR AS varchar) + ' occurence(s)' AS 'Outcome'

END

Open in new window

0
 

Author Comment

by:tconnelly
ID: 20401417
I tested both mods of the script and hit the same error.
The statement has been terminated.
Msg 8152, Level 16, State 14, Line 1
String or binary data would be truncated.
The Error would repeat 5 times and the proc ends. Could this have something to do with runing this on SQL 2005.

Also Rajesh_mj can you please explain what your edits improve over the first suggestion

Thanks

Tom

Thanks

Also
0
 
LVL 25

Expert Comment

by:imitchie
ID: 20401444
tconnelly,
rajesh has moved the disable/enable trigger to affect only tables that will perform search/replace. no big deal really

String or binary data would be truncated.
the error is because some of your fields are already maxed out. i.e.
varchar(10) --- ABCDEFGHIJ
if you perform exec searchandreplace 'ABC', 'ABCZZZ'
then it's trying to change it to ABCZZZDEFGHIJ   <<< this is longer than the field length of varchar(10)
0
 

Author Comment

by:tconnelly
ID: 20401602
OK I did some more testing and found that the first error I posted only happens on SQL2005.

On sql 2000 I got this:

Server: Msg 547, Level 16, State 1, Line 1
UPDATE statement conflicted with TABLE FOREIGN KEY constraint 'FK_mbbmPlanSheet_Status'. The conflict occurred in database 'boston', table 'mbbmSheetStatusCode'.
Server: Msg 547, Level 16, State 1, Line 1
UPDATE statement conflicted with TABLE REFERENCE constraint 'FK_mbbmPlanSheet_Status'. The conflict occurred in database 'boston', table 'mbbmPlanSheet'.
The statement has been terminated.
The statement has been terminated.

I am guessing that this is happing becuse some of the replacements are foreign or primacy keys. I tested this on a much more basic table and did not get hits error.


So would it be possible to make this work on SQL2005 and like we did with the triggers can we disable any KEY constraints or TABLE REFERENCE constraints at the start and enable then again at the end to avoid this error ?
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:tconnelly
ID: 20401619
imitchie,

In regards to your last reply
if i perform exec searchandreplace 'ABC', 'ABCZZZ' I would only want it to replace that exact value.

for example:

If I perform exec searchandreplace 'tom', 'tommy'   I dont want it to find  tomorrow and make it tommyorrow.  If that is currently how it is working I would like to change that also.

TC




String or binary data would be truncated.
the error is because some of your fields are already maxed out. i.e.
varchar(10) --- ABCDEFGHIJ
if you perform exec searchandreplace 'ABC', 'ABCZZZ'
then it's trying to change it to ABCZZZDEFGHIJ   <<< this is longer than the field length of varchar(10)
0
 
LVL 25

Expert Comment

by:imitchie
ID: 20401691
tconnelly - unfortunately, that's not what your procedure does!

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

and

SET @SQL=
  'UPDATE ' + @TableName + ' SET ' + @ColumnName
  + ' =  REPLACE(' + @ColumnName + ', ' + QUOTENAME(@SearchStr, '''') + ', '
  + QUOTENAME(@ReplaceStr, '''') + ')
  WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2

assure me that it will do as I said.  Also, even if you were searching for "TOM" and replacing with "TOMMY", if TOM happens to be on a table with code of CHAR(3), you will get the same "String or binary data would be truncated." error, due to the field not being large enough
0
 
LVL 25

Accepted Solution

by:
imitchie earned 500 total points
ID: 20401702
I'm sure I have solved your original question, but it seems to have evolved.
There seems to be quite a number of gotchas turning up:

String or binary data would be truncated
KEY Constraints

not to mention this one you have not covered
Column CHECK Constraints
Option for Full String replacement ("tom" must match "tom" exactly)

It looks like you are after a new procedure more than a small modification to the existing one.
0
 

Author Comment

by:tconnelly
ID: 20404179
Honestly I dont care if it is a new procedure or a mod of this one as long as in the end it meets the requirements. 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 name to replace in.

So do the search and only update columns like company_id or comp_id. It would have to work for a range of column names as its not exactly the same in all tables.
0
 

Author Closing Comment

by:tconnelly
ID: 31412286
imitchie was very helpful he went beyond the scope of my origanl question. I still have some other issues but i will address that in a new question.

0
 
LVL 25

Expert Comment

by:imitchie
ID: 20409563
Thank you kindly for the nice remarks! Good luck with your stored procedure.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

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.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

708 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

17 Experts available now in Live!

Get 1:1 Help Now