Problems with sp_rename
Unsupported or undocumented functions are not surprising in IT software. SQL Server also has some, for example very useful sp_who2. If it suddenly stops working, do not blame Microsoft - they have never encouraged you to use it. Leaving undocumented commands behind, there are also stored procedures that are well documented and supported but not recommended to use. One of them is sp_rename which I am writing about.
This stored procedure is available in SQL Server. It is convenient because it can rename various database objects like columns, index, objects.
exec sp_rename 'MyDb.MyTable.MyColumn', 'RenamedColumn', 'COLUMN';
exec sp_rename 'MyDb.MyTable.IX_index', 'IX_renamedINdex', 'INDEX';
exec sp_rename 'MyTable', 'RenamedTable', 'TABLE';
These are only examples. There are more like userdatatypes, constraints, statistics.
Would you like to learn Liquibase in an organized way? Enroll to my course on Udemy.
October promotion - $13.
Promo code: OCT13USD
Problems with sp_rename
In spite of it seems useful, even Microsoft does not recommend using it in some cases. Let me show you why.
I create a simple view.
create view SOME_VIEW as
select 1 as COL
SQL Server has sql_modules and syscomments system views with meta information about database objects. Let's see what is in there for my table.
where object_id = OBJECT_ID('SOME_VIEW');
where id = OBJECT_ID('SOME_VIEW');
They both return the same SQL query I used to create the view. That was expected.
The view can be easily renamed with sp_rename.
exec sp_rename 'SOME_VIEW', 'RENAMED_VIEW';
Although it returns a warning:
Caution: Changing any part of an object name could break scripts and stored procedures.
the view seems renamed.
It can be queries by the new name.
(2 row(s) affected)
I would not se the word problems in the post title if it all worked well. Something disturbing happened to the system views.
where object_id = OBJECT_ID('RENAMED_VIEW');
where id = OBJECT_ID('RENAMED_VIEW');
Definitions in the system views were not updated. It means that the object changed its name only physically without updating all its metadata.
Is it important? In most cases, probably not, but I often tried to find an object definition using these system views which in this case show incorrect information. If you have to rename an object, just drop it and recreate it.