Incorrect Stored Procedure Name generated

• 
  My guess is that SQL Compare is using the syscomments table to generate the procedure script.
  
  When you rename a procedure , the original procedure is removed from sysobjects and a new object is created in sysobjects with the new name and a new object_id. The object_id is related to syscomments.id which stores the text of the original stored procedure.
  
  The problem occurs when the syscomments text column fails to get updated with the rename. When doing a sp_rename, or renaming from within Management Studio, the syscomments table text data will exist as it did under the orginal object_id.
  
  An example can be seen below:
  
  
  

  if object_id('original') is not null begin
  	drop procedure original
  end
  go
  
  create procedure original
  as
  
  select 1
  
  go
  
  select	[text]
  from	syscomments
  where	id = object_id('original')
  
  go
  
  if object_id('notoriginal') is not null begin
  	drop procedure notoriginal
  end
  go
  
  
  exec sp_rename 'original', 'notoriginal'
  go
  
  
  
  select	[text]
  from	syscomments
  where	id = object_id('notoriginal')
  
  go
  

  
  
  
  In order to avoid this issue, you need to script the renamed stored procedure and execute it against the database.
  
  So, the renaming procedure would be as follows:
  
  1. rename in management studio, or execute sp_rename
  2. in management studio, right click and script to a new window.
  3. execute the script.
  
  syscomments should now be updated with the proper text.

  tferreira April 12, 2006 14:44

  - 0 +
• 
  You're right. SQL Compare is getting the script from syscomments. Enterprise manager and sp_rename don't update syscomments. This causes no end of problems for SQL Compare when updating views and stored procedures!

  Brian Donahue April 14, 2006 10:35

  - 0 +
• 
  I've written a SQL Query that helps you identify the items in sysobjects and syscomments that are out of sync as a result of renaming an object.
  
  Run the query below to identify the items, then generate create scripts for each of the relevant items, delete them and then recreate them with script. This should bring them back into line again.
  There may be more automated ways of doing this, but it works.
  
  USE dbname
  GO
  
  SELECT so.id, so.name, so.type, sc.text, PATINDEX ( '%' + so.name + '%' , sc.text )
  FROM
  sysobjects as so
  join syscomments as sc on so.id = sc.id
  WHERE PATINDEX ( '%' + so.name + '%' , sc.text ) < 1
  AND sc.colid = 1
  and so.type <> 'D'
  ORDER BY so.name

  tonyb June 08, 2007 09:50

  - 0 +
• 
  SQL Compare version 6, so I've been told, will reconcile this issue.
  *And the crowd cheered!*

  Brian Donahue June 20, 2007 18:27

  - 0 +

Add comment

Please sign in to leave a comment.