How to rename a table in SQL Server?

Question

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'.

Answer 1

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';

Answer 2

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

exec sp_rename 'schema.old_table_name', 'new_table_name'

Answer 3

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

Answer 4

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..

Answer 5

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';

Answer 6

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