Changing a table name in Microsoft SQL Server is a very easy task. Microsoft has a great built-in stored procedure we can use to easily change a table name.
The stored procedure is called SP_RENAME. In this very brief tutorial, we’ll discuss how to use the SP_RENAME stored procedure and outline some very important rules you need to know when using this procedure.
We’ll discuss these topics:
- The SP_RENAME system stored procedure
- An example of changing a table name
- The 4 rules you need to remember about SP_RENAME
- Links
Let’s take it from the top.
1. The SP_RENAME system stored procedure
To change a table name in SQL Server, we use the SP_RENAME system stored procedure. There are 3 parameters to this stored procedure, but when it comes to changing a table name, you only need to specify 2 of them. Those two parameters are:
1. @objname:
This is the name of the table we want to change. When we specify the table name, we need to do so using the two part identifier. That means we include the name of the schema the table belongs to. An example would be ‘dbo.EmployeeDetails’. The schema is dbo, and the table within that schema (the table we want to change) is EmployeeDetails. When we specify a value for this parameter, we wrap it around single quotes.
2. @newname:
This is of course the new name we want to give our table. When we specify the new name, we DO NOT use the two part identifier. The new table will automatically be part of the schema identified in the first parameter. We wrap the value around single quotes, too.
The third, optional parameter is @objtype. When it comes to changing a table name, the value for this parameter needs to be NULL. It’s usually easier, however, to simply leave out this parameter completely.
2. An example of changing a table name
Let’s create a simple table:
CREATE TABLE EmployeeDetails ( EmplID INT IDENTITY(10, 5), FirstName VARCHAR(20), LastName VARCHAR(20) )
Notice that since we didn’t specify a schema, the table is automatically put in the ‘dbo‘ schema.
Now we’ll insert some data into this table:
INSERT INTO EmployeeDetails (FirstName, LastName)
VALUES
('Sara','Connor'),
('Ricky','Goldsworth'),
('George','Orwell'),
('Amy','Bennett')
Awesome, the data in the table should look like this:
Ok, so let’s say we messed up and actually meant to name the table ‘EmployeeInfo‘. If we want to change the table name from ‘EmployeeDetails‘ to ‘EmployeeInfo‘ using the SP_RENAME stored procedure, the syntax would be:
EXECUTE SP_RENAME @objname = 'dbo.EmployeeDetails', @newname = 'EmployeeInfo'
So remember what we discussed earlier:
- You need to outline the schema name in the first parameter.
- You DO NOT outline the schema name in the second parameter.
- There is no third parameter. I mean, you can outline the third parameter, but the value needs to be NULL. It’s just easier to simply leave out the third parameter, in my opinion.
If we run that statement, we see our table was successfully changed:
When the stored procedure executes successfully, we get this ‘Caution’ message. More on that in a bit.
Now if we query ‘EmployeeInfo‘, we see all our data is there!
Nice!
3. The 4 rules you need to remember about SP_RENAME
There are some important rules you need to understand about the SP_RENAME system stored procedure. We have already discussed some of these rules, but we’ll go into more detail now.
Rule # 1: Don’t specify a schema for your destination table name
We talked about this earlier, but it’s very important. You specifically DO NOT outline a schema name with your destination table name. The schema name is not an optional thing for the second parameter. If you outline a schema name, you will break things.
For example, let’s say I want to change our table again to ‘EmployeeData‘. I’ll demonstrate the terrible thing that can happen when you outline a schema in your second parameter:
(WARNING: DO NOT RUN THIS CODE IN YOUR ENVIRONMENT!)
EXECUTE SP_RENAME @objname = 'dbo.EmployeeInfo', @newname = 'dbo.EmployeeData'
I outlined the schema ‘dbo‘ in my second parameter, which is the schema that the new table would have been put into anyway if I just left it alone. But I decided to be explicit and outline the schema name anyway.
If I run that statement, it looks like it succeeded. We get that same ‘Caution’ message from earlier:
Now let’s try to query the new table…..
Hmm, ok, well maybe the stored procedure didn’t actually work. Let’s check to make sure our old table name still exists…..
Holy f*cking sh*t.
Where the f*ck is the table? The new table doesn’t exist, nor does the old table. So where is my data? If I look at sys.tables, I can see that the table exists somewhere:
But folks, I don’t know how to fix this. Maybe I’ll figure it out and create a separate post about it. But for now, your guess is as good as mine!
So again, for the love of God,
Don’t specify a schema name in your second parameter under any circumstances (Microsoft, do better).
Rule # 2: When using SP_RENAME to change the name of a table, we don’t outline a third parameter
I mentioned this earlier, but we’ll talk about it in more detail. Normally, when using the SP_RENAME stored procedure, you specify three parameters:
- @objname
- @newname
- @objtype
But when using SP_RENAME to change a table name specifically, you can choose to leave off the third parameter completely. If you choose to keep it, the value needs to be NULL, like this:
EXECUTE SP_RENAME @objname = 'dbo.EmployeeDetails', @newname = 'EmployeeInfo', @objtype = NULL
Which leads us to our next rule…
Rule # 3: There are other uses of SP_RENAME
The SP_RENAME procedure can change more than just table names. For example, you can use SP_RENAME to change a column name if you need to.
Besides tables and columns, there are several other objects in SQL Server that can be renamed using this procedure. For the other objects, you would simply specify the appropriate @objtype value. Here is the full list of @objtype values you can specify, which indicates all the different objects you can change by using SP_RENAME. This list is pulled directly from the official Microsoft documentation:
- COLUMN
- DATABASE
- INDEX
- OBJECT
- STATISTICS
- USERDATATYPE
This stored procedure is definitely a good one to know.
Rule # 4: SP_RENAME will not update references to the old table name
Finally, we need to understand that SP_RENAME will change your table name, but it won’t change any existing references to that table name for you automatically.
Remember that ‘Caution’ message we were seeing when running the procedure?:
This is what the message is talking about.
For example, let’s say the table ‘EmployeeDetails‘ were referenced in a View. The SP_RENAME procedure will go ahead and change the name of the table, but it won’t edit the View to reference the new name of the table. You will need to make that change yourself.
Of course, a table can be referenced in many places, so the task of finding all those references and changing them might be huge. But it must be done!
Here is a helpful query to locate all objects that reference a specific word. That word can, of course, be a table name that you intend to change:
SELECT ss.name as 'Schema', so.name as 'Object', so.type as 'Object Type' FROM sys.objects as so INNER JOIN sys.schemas as ss ON so.schema_id = ss.schema_id WHERE so.name in (select object_name(id) from syscomments where text like '%WordToLookFor%') order by so.name
4. Links
Here is a link to the official Microsoft documentation on SP_RENAME (which I’m sad to say doesn’t discuss many of the important rules I have discussed here): SP_RENAME(Transact-SQL)
Next Steps:
Leave a comment if you found this tutorial helpful!
As I mentioned earlier, we can use SP_RENAME to change the name of a column if we need to. I have a full tutorial on how it’s done. Check it out:
How to change a column name in SQL Server
Also, if you need to know a thing or two about Stored Procedures in SQL Server, check out these beginner-friendly tutorials I have on the topic:
SQL Server Stored Procedure: The Ultimate Guide for Beginners
Stored procedure with parameters: A guide for beginners
Thank you very much for reading!
Make sure you subscribe to my newsletter to receive special offers and notifications anytime a new tutorial is released!
I hope you found this tutorial helpful. If you have any questions, leave a comment. Or better yet, send me an email!
