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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

This is what I use   EXEC sp rename  MyTable    MyTableNewName

User · Answer

To rename a table in SQL Server  use the sp rename command   exec sp rename  schema old table name    new table name

[sql-server] How to rename a table in SQL Server?

Examples related to sql-server

Examples related to rename

Examples related to alter-table