464

The SQL query that I have used is :

ALTER TABLE oldtable RENAME TO newtable;

But, it gives me an error.

Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'TO'.

Shin
  • 664
  • 2
  • 13
  • 30
Switch
  • 14,783
  • 21
  • 69
  • 110

7 Answers7

847

To rename a table in SQL Server, use the sp_rename command:

exec sp_rename 'schema.old_table_name', 'new_table_name'
Kols
  • 3,641
  • 2
  • 34
  • 42
Jeff Hornby
  • 12,948
  • 4
  • 40
  • 61
  • 18
    One more thing: if any of the table names has a `.` in them, use `[]` around the table name. (I know, I know, but dots can happen...) E.g. `sp_rename '[Stupid.name]', 'NewName'` or with schema `sp_rename '[dbo.Stupid.name]', 'NewName'` – vacip Jul 25 '16 at 14:44
  • 92
    And to add, _don't_ accidentally put the schema in the `'NewName'` field, otherwise your table might end up looking something like `dbo.dbo.NewName`. – Michael Plautz Jan 23 '17 at 21:12
  • 5
    Keep in mind that when renaming a table, you almost certainly want to **also rename any references to that table that may exist in stored procedures, views, functions, etc.** A quick google can find one of the many tools that can do this for you. Or you can use a script that finds a given string in all these objects, and paste them as ALTER statements, and do a find-and-replace, then run them all. – MGOwen Apr 19 '17 at 06:33
  • 2
    You can also create a synonym named after the old table name pointing to the new table `CREATE SYNONYM [schema].[synonymName] FOR [schema].[tableName]` – Ruskin Apr 03 '19 at 05:28
  • 5
    do not place new name in square brackets! otherwise the table will have square brackets IN the name. So: 'new_table_name' - is correct, '[new_table_name]' - will get you into trouble – VeganHunter Apr 08 '20 at 10:35
  • 1
    @vacip if you say `sp_rename '[dbo.Stupid.name]'` the operation will fail. Correct: `sp_rename '[dbo].[Stupid.name]'` – Jari Turkia Jun 23 '21 at 11:25
163

To rename a column:

sp_rename 'table_name.old_column_name', 'new_column_name' , 'COLUMN';

To rename a table:

sp_rename 'old_table_name','new_table_name';
johnnyRose
  • 7,310
  • 17
  • 40
  • 61
Ravi Kumar
  • 1,903
  • 1
  • 11
  • 17
  • Just a note here that `sp_rename` for Azure Synapse Analytics SQL Pools only supports renaming a column. No support yet for renaming tables or indexes. https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-rename-transact-sql?view=sql-server-ver16 – TrailJon Aug 07 '23 at 17:43
27

Table Name

sp_rename 'db_name.old_table_name', 'new_table_name'

Column

sp_rename 'db_name.old_table_name.name', 'userName', 'COLUMN'

Index

sp_rename 'db_name.old_table_name.id', 'product_ID', 'INDEX'

also available for statics and datatypes

budamivardi
  • 722
  • 5
  • 10
  • 3
    For Column you're missing a comma between first and second parameters. It should be: sp_rename 'db_name.old_table_name.name', 'userName', 'COLUMN' – sebastian.roibu Oct 14 '19 at 16:23
16

When using sp_rename which works like in above answers, check also which objects are affected after renaming, that reference that table, because you need to change those too

I took a code example for table dependencies at Pinal Dave's blog here

USE AdventureWorks
GO
SELECT
referencing_schema_name = SCHEMA_NAME(o.SCHEMA_ID),
referencing_object_name = o.name,
referencing_object_type_desc = o.type_desc,
referenced_schema_name,
referenced_object_name = referenced_entity_name,
referenced_object_type_desc = o1.type_desc,
referenced_server_name, referenced_database_name
--,sed.* -- Uncomment for all the columns
FROM
sys.sql_expression_dependencies sed
INNER JOIN
sys.objects o ON sed.referencing_id = o.[object_id]
LEFT OUTER JOIN
sys.objects o1 ON sed.referenced_id = o1.[object_id]
WHERE
referenced_entity_name = 'Customer'

So, all these dependent objects needs to be updated also

Or use some add-in if you can, some of them have feature to rename object, and all depend,ent objects too

14

If you try exec sp_rename and receieve a LockMatchID error then it might help to add a use [database] statement first:

I tried 

 exec sp_rename '[database_name].[dbo].[table_name]', 'new_table_name';
 -- Invalid EXECUTE statement using object "Object", method "LockMatchID".

What I had to do to fix it was to rewrite it to:

use database_name
exec sp_rename '[dbo].[table_name]', 'new_table_name';
Nickolay
  • 31,095
  • 13
  • 107
  • 185
Stian
  • 1,221
  • 1
  • 19
  • 26
7

To change a table name with a different schema:

Example: Change dbo.MyTable1 to wrk.MyTable2

EXEC SP_RENAME 'dbo.MyTable1', 'MyTable2'

ALTER SCHEMA wrk TRANSFER dbo.MyTable2
live-love
  • 48,840
  • 22
  • 240
  • 204
1

Nothing worked from proposed here .. So just pored the data into new table

SELECT * 
INTO [acecodetable].['PSCLineReason']
FROM [acecodetable].['15_PSCLineReason'];

maybe will be useful for someone..

In my case it didn't recognize the new schema also the dbo was the owner..

UPDATE

EXECUTE sp_rename N'[acecodetable].[''TradeAgreementClaim'']', N'TradeAgreementClaim';

Worked for me. I found it from the script generated automatically when updating the PK for one of the tables. This way it recognized the new schema as well..

Tzvi Gregory Kaidanov
  • 3,080
  • 3
  • 26
  • 33