sp_rename to change schema?

words: 519

Reading time: ~2.5 minutes

The Set Up:

Recently I was asked by a developer whether they could use sp_rename to change the schema of a table.

I said no but I realised that I don’t know for sure as I’ve never tried it this way.

Granted I have never needed to when we have such a descriptive command like ALTER SCHEMA.

So I tested to see if sp_rename could change the schema of a table and thought I would share my results.

Here they are:

---

SP_RENAME:

Script 1:

SELECT [Schema Name] = SCHEMA_NAME([schema_id]),
 [Table Name] = [name]
 FROM sys.tables
 WHERE [name] = N'Alphanumeric';

Now taking a look at the documentation for “sp_rename”, turns out all we need is

1. the current name,
2. the new name we want to call it, and
3. an optional object type (which I’ll include because I like typing).

So with that, it seems simple to run the following…

Script 2:

 EXEC sp_rename
 @objname = N'dbo.Alphanumeric',
 @newname = N'deleteable.Alphanumeric',
 @objtype = 'OBJECT';

So now all there is left to do is check if it worked, so we run our first script again and we get???:

I repeat the above: eh…what???

Where did my table go???

Please tell me I didn’t delete the table? It’s a test system and I took a backup before starting but it’s a whole lot of hassle to recreate the table.

However, on a whim, I changed my first query to use a LIKE:

Script 3:

So I haven’t changed the schema? I’ve renamed it to be dbo.deletable.Alphanumeric?

Is that even query-able?

SELECT * FROM dbo.deleteable.Alphanumeric; -- Fails!

SELECT * FROM [dbo].[deleteable.Alphanumeric]; -- Works!

Okay, let’s just change it back quickly and pretend it never happened:

Script 4:

EXEC sp_rename
@objname = N'deletable.Alphanumeric',
@newname = N'Alphanumeric',
@objtype = 'OBJECT';

Okay, okay maybe it’s like the SELECT statement and I need to wrap it in square brackets?

Script 5:

EXEC sp_rename
@objname = N'[deletable.Alphanumeric]',
@newname = N'[Alphanumeric]',
@objtype = 'OBJECT';

Maybe we’re being too specific?

Script 6:

EXEC sp_rename '[deleteable.Alphanumeric]', 'Alphanumeric';

A quick run of our first script to confirm?

Now, as to why that syntax works but the others don’t…I have no idea.

I will try and figure that out (fodder for another blog post 🙂 ) but I’m going to need a few more coffees before I go touch that again.

---

ALTER SCHEMA:

It’s a bit sad though… all that hassle for something that didn’t even work in the end?

Now, lets check out the documentation of “ALTER SCHEMA”.

1. where we are changing it to, and
2. what we’re changing.

Seems simple, but then so did sp_rename and that burnt me.

Script 7:

A quick check to see if it actually worked as I’m not swayed anymore just by a lack of warnings:

---

Sum it up:

If I didn’t know the answer at the start, I definitely do now.

Can you change the schema of an object by using “sp_rename”?

Hell no.

Save yourself the hassle and just stick to ALTER SCHEMA. It’s easier, believe me.