SQL Server - Ways of renaming a table name

Question

I want to rename a table in SQL Server. I know the proper syntax would be:

sp_rename 'old table name','new table name';

Would it make any difference if I write it this way?:

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

I tried running it on SQL Server and without the EXEC, it would highlight my syntax sp_rename with red, but it doesn't throw any error.

Can anyone suggest the proper way or any other alternatives to rename a table?

Note: I know altering table name will affect or probably break the script and stored procedure, is there any way to prevent this? Or it only breaks if there is another table dependent on it?

if you have other sql statement within the same query window, you will need to use `exec`. Your first statement (without exec) works only if it is executed by itself — Squirrel, Nov 07 '17 at 05:10
So if I want to rename,say 40 tables inside 1 SQL script, I should add EXEC to every 40 statements ? — Cookie Monster, Nov 07 '17 at 06:48
Are you sure? According to Jaya, he said it's not mandatory. — Cookie Monster, Nov 08 '17 at 02:25

score 1 · Accepted Answer · answered Nov 07 '17 at 04:42

1

You can execute a stored procedure with or without the EXEC Keyword. So Both your approaches are correct and has the same effect.

All the below 3 approaches are valid but the most commonly used is the 1st one

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

EXECUTE sp_rename 'old table name','new table name';

sp_rename 'old table name','new table name';

answered Nov 07 '17 at 04:42

Jayasurya Satheesh

7,826
3
22
39

What if I want to rename a few tables in the same query editor window, do I still need to include EXEC? – Cookie Monster Nov 07 '17 at 06:53
No, But each rename should be called separately. EXEC is not madatory – Jayasurya Satheesh Nov 07 '17 at 06:56

score 1 · Answer 2 · answered Nov 07 '17 at 05:20

1

The easiest way would be to right click on the table name and click "rename". Both of your methods of using a proc are correct, though.

I would caution using this procedure though, especially in renaming stored procedures. It has been documented in many places that sp_rename fails to update the sys.procedures table which is often used to identify these objects within your database.

answered Nov 07 '17 at 05:20

S3S

24,809
5
26
45

So it's safer to use sp_rename "" , "" ? – Cookie Monster Nov 07 '17 at 06:46
You're fine doing it your way for tables. But for procedures, you'd want to drop and create to – S3S Nov 07 '17 at 14:05

SQL Server - Ways of renaming a table name

2 Answers2