Solved

Help modifying a SQL find and replace Stored procedure

Posted on 2007-12-03
11
538 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

 
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
 

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

 Database Backup and Recovery Best Practices

Join Percona’s, Architect, Manjot Singh as he presents Database Backup and Recovery Best Practices (with a Focus on MySQL) on Thursday, July 27, 2017 at 11:00 am PDT / 2:00 pm EDT (UTC-7). In the case of a failure, do you know how long it will take to restore your database?

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
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.

626 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