I’ve noticed people before struggling using sp_rename with tables that aren’t in the default schema. Many people don’t use schemas, so there’s often confusion, when they finally do come across the need to rename a table belonging to another schema.

Assuming the below ‘Suppliers’ table is in the users default schema (usually dbo) then the following will work as expected.

EXEC sp_rename 'Suppliers', 'Suppliers2';

Caution: Changing any part of an object name could break scripts and stored procedures.

If the table had not been in the users default schema the following error would have occurred.

Msg 15225, Level 11, State 1, Procedure sp_rename, Line 338
No item by the name of 'Suppliers' could be found in the current database 'test', given that @itemtype was input as '(null)'.

After encountering this many people then figure, quite correctly, that the schema should be referenced but get it slightly wrong.

EXEC sp_rename 'AnotherSchema.Suppliers', 'AnotherSchema.Suppliers2';

This works, but not in the way you want, you end up with a table called AnotherSchema.Suppliers2. This can then only be referenced by using the following structure; AnotherSchema.[AnotherSchema.Suppliers2]. The correct syntax is;

EXEC sp_rename 'schema.old table name', 'new table name';

For example;

EXEC sp_rename 'AnotherSchema.Suppliers', 'Suppliers2';