Solved

Help modifying a SQL find and replace Stored procedure

Posted on 2007-12-03
11
528 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL STANDARD CORE 8 38
LTrim & Double Space Correction 5 40
fault SQL backup files that wont restore - how common 3 26
SQL Log size 3 18
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
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…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

828 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