Using sp_rename with schemas

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';

6 Comments

  1. Davos says:

    But, what if you want to change the schema as part of the rename?

  2. Rhys says:

    Hi Dave,

    I don’t think you can do this via sp_rename. You’ll need to use the ALTER SCHEMA new_schema TRANSFER old_schema.table_name as an additional step.

    Cheers,

    Rhys

  3. Rhys says:

    Hi Dave,

    You can’t with sp_rename. You’ll need to use a statement like ALTER SCHEMA new_schema TRANSFER old_schema.table_name as an additional step.

    Cheers,

    Rhys

  4. Davos says:

    Thanks, I did exactly that

  5. Jere DeLaune says:

    I found that simply putting the schema in the first parameter was not working. Don’t have time to dig into why but I still came up with the error that the object was not found. Here’s the example of what didn’t work:
    EXEC sp_rename ‘jsx.HIST’, ‘HISTOLD’;

    When I added the brackets to the table schema and table, it worked perfectly:
    EXEC sp_rename ‘[jsx].[HIST]’, ‘HISTOLD’;

    Not sure why and don’t have time to dig but this works perfect.

  6. Rhys says:

    What version of SQL are you running?

    Either jsx or HIST are reserved keywords in TSQL. Square brackets escape them allow you to use them as object names. Try creating a table called “SELECT”.

Leave a Reply