Activity overview
Latest activity by tferreira
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.
/ comments
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 cre...