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.
sp_rename usage
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.
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
union all
select 2;
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.
select *
from sys.sql_modules
where object_id = OBJECT_ID('SOME_VIEW');
select *
from sys.syscomments
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.
select *
from RENAMED_VIEW
COL
-----------
1
2
(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.
select *
from sys.sql_modules
where object_id = OBJECT_ID('RENAMED_VIEW');
select *
from sys.syscomments
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.
