Help modifying a SQL find and replace Stored procedure

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
tconnellyAsked:
Who is Participating?
 
imitchieConnect With a Mentor Commented:
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
 
imitchieCommented:

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
 
Rajesh_mjCommented:
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
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
tconnellyAuthor Commented:
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
 
imitchieCommented:
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
 
tconnellyAuthor Commented:
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
 
tconnellyAuthor Commented:
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
 
imitchieCommented:
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
 
tconnellyAuthor Commented:
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
 
tconnellyAuthor Commented:
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
 
imitchieCommented:
Thank you kindly for the nice remarks! Good luck with your stored procedure.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.