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:
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
- the current name,
- the new name we want to call it, and
- an optional object type (which I’ll include because I like typing).
So with that, it seems simple to run the following…
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:
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:
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?
EXEC sp_rename @objname = N'[deletable.Alphanumeric]', @newname = N'[Alphanumeric]', @objtype = 'OBJECT';
Maybe we’re being too specific?
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.
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”.
- where we are changing it to, and
- what we’re changing.
Seems simple, but then so did sp_rename and that burnt me.
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”?
Save yourself the hassle and just stick to ALTER SCHEMA. It’s easier, believe me.