Object name to Definition mismatch
mikeATCompuware
Posts: 9
There appears to be an issue when syncing objects where the underlying definition does not match the object name.
For instance, you have a view object named v_users and its definition is:
create view dbo.v_users
AS
select user_name, user_id from users
Now if you rename the view object to v_users2, the underlying view definition remains 'create view dbo.v_users AS...'
In addition to views, I think this issue applies to stored procedures and maybe other objects. Also, if the owner is changed, a similar issue will occur.
For instance, you have a view object named v_users and its definition is:
create view dbo.v_users
AS
select user_name, user_id from users
Now if you rename the view object to v_users2, the underlying view definition remains 'create view dbo.v_users AS...'
In addition to views, I think this issue applies to stored procedures and maybe other objects. Also, if the owner is changed, a similar issue will occur.
Comments
You are absolutely right. And unfortunately this is a sideeffect of sp_rename and of operations that change the owner of a stored procedure, view, function, etc. For this reason it is advised to avoid sp_rename (or to use it with extreme care). Basically this means that the database will be in a sort of inconsistent state (its stored procedure definition is not reflecting the statement that shoud be used to create it again). In the current version we do not support such inconsistent databases. The better way of renaming stored procedures is to drop them, and create them again.
Regards,
Andras
Red Gate Software Ltd.