Renaming tables, that’s easy right?
Renaming a Table
Performing the rename of a table is straight forward in Management Studio via Object Explorer. You can simply select the table and right click and Rename (or press F2) and edit the name right there and then. Hit return and we’re good to go.
If you’re scripting it for a database migration or upgrade though we have a built in procedure to deal with this:
EXEC sp_rename 'TableName', 'NewTable';
Again this looks straight forward however there are a few things to note. Firstly the original table name has the schema name optional so you could use either dbo.TableName or TableName. That said, the new table name should not include the schema name – if you do then you’ll end up with something similar to dbo.dbo.NewTable. Ugh.
Now if you’re in this situation then you could just rename it back right? EXEC sp_rename 'dbo.dbo.NewTable', 'NewTable';
Well no, because if you’ve got a table name with a period character in then you’ll be getting an error thrown up telling you No item by the name of 'dbo.dbo.NewTable' could be found. If you need to rename a table with a period in the name then you need to quote it:
EXEC sp_rename '"dbo.dbo.NewTable"', 'NewTable';
Yep that’s double quotes inside the single quotes. The schema name is again optional for the source table.
Changing the Schema
Schemas are slightly different, you can’t do them via the Object Explorer panel but that doesn’t mean you can’t do them via the Management Studio UI. If we pop into the Table Designer and open the Properties for the table (or press F4 to bring these up) then the Schema is one of the options we can change here:
As schemas are a security feature if you change this you’ll get a warning about permissions being removed from the object when this is changed. Doing this via the designer isn’t effective immediately like renaming via Object Explorer so the table needs to be saved to take effect.
Again there’s an alternative approach by using a statement however this one is a command against the schema rather than a procedure:
ALTER SCHEMA new TRANSFER dbo.NewTable;
Rather than specifying the old then new as with renaming, for changing schema we request to alter the target schema and to transfer the requested object into it. The same conditions apply around permissions being removed since this is the same feature the designer will use.
What About References?
In both of these situations it’s good to know that foreign key relationships will remain in place following the changes. These are established via object IDs if you check out the DMV sys.foreign_keys and renaming the table doesn’t impact these.
Where you may have issues is with views and stored procedures. These won’t ordinarily stop you renaming or transferring tables however next time that you come to use them they’ll be broken. You may find some of these with the option of using WITH SCHEMABINDING for views (or natively compiled stored procedures) which will then stop the table being renamed but this isn’t guaranteed to be in place. It’s worth checking your dependencies carefully and testing thoroughly before performing any changes.
Andy Brownsword 