BUG: Renamed Objects have original name in DDL Pane
DWolford1234
Posts: 44
I stumbled acrossed this earlier, was able to reproduce the issue, and thought I'd share.
I haven't tested if this is true with all object types, but it was reproducable with Stored Procedures.
First, I create a simple SP:
CREATE PROCEDURE dantest1
AS
SELECT *
FROM dbo.ImportLog
WHERE date = '03/01/2010'
Then, I used the Object explorer to right click the dantest1 proc and clicked rename. I renamed it to dantest1_old.
Next I run the Create script again:
CREATE PROCEDURE dantest1
AS
SELECT *
FROM dbo.ImportLog
WHERE date = '03/01/2010'
Now, if I do a search on 03/01/2010, both dantest1 and dantest1_old are returned in the results.
However, if you look at the DDL, they both say
CREATE PROCEDURE dantest1
Now, If i run an
ALTER PROCEDURE dantest1_old
AS
SELECT *
FROM dbo.ImportLog
WHERE date = '03/01/2010'
everything works.
Somehow, using the explorer to rename the objects isn't updating a reference that SQL Search uses to build the DDL.
I'll let you see if it happens on tables, etc.
Thanks for the tools!
Dan
I haven't tested if this is true with all object types, but it was reproducable with Stored Procedures.
First, I create a simple SP:
CREATE PROCEDURE dantest1
AS
SELECT *
FROM dbo.ImportLog
WHERE date = '03/01/2010'
Then, I used the Object explorer to right click the dantest1 proc and clicked rename. I renamed it to dantest1_old.
Next I run the Create script again:
CREATE PROCEDURE dantest1
AS
SELECT *
FROM dbo.ImportLog
WHERE date = '03/01/2010'
Now, if I do a search on 03/01/2010, both dantest1 and dantest1_old are returned in the results.
However, if you look at the DDL, they both say
CREATE PROCEDURE dantest1
Now, If i run an
ALTER PROCEDURE dantest1_old
AS
SELECT *
FROM dbo.ImportLog
WHERE date = '03/01/2010'
everything works.
Somehow, using the explorer to rename the objects isn't updating a reference that SQL Search uses to build the DDL.
I'll let you see if it happens on tables, etc.
Thanks for the tools!
Dan
Comments
David Atkinson
Red Gate Software
Product Manager
Redgate Software
I've been trying to search in Google for "sp_rename bug SSMS fix" and suchlike to find a fix reference but to no avail. I did, however, come across a blog posting that explains the issue:
http://www.adrianbanks.co.uk/?p=24
I think the conclusion is that the best approach is to avoid the rename feature. Red Gate's SQL Refactor has a 'Smart Rename' feature that does this properly (as well as renaming all references throughout the schema), although this is a commercial tool.
David
Product Manager
Redgate Software
Oh well. Thankfully, I just purchased the Developer Bundle, which includes SQL Refactor, so I'm good to go.
Unfortunately, I think you'll have others seeing this as a problem, even though it may not, technically, be your fault.
Good luck.
Thanks,
Dan
David
Product Manager
Redgate Software