5

I know I can accomplish my objective with this process:

  1. Create new column
  2. Update new from old
  3. Drop old

I am looking for a way to do this with one command. I know that remane object works at the table level. For example, I can do this:

rename object Test.danPatient to dimPatient

But these all fail with various error messages:

rename object Test.dimPatient.City to Test.dimPatient.Town
rename object Test.dimPatient.City to Town
rename object DatabaseName.Test.dimPatient.City to Town
rename object DatabaseName.Test.dimPatient.City to DatabaseName.Test.dimPatient.Town

Plus, according to this, sp_rename is not supported by Azure Data Warehouse. My unsuccessful efforts to use it suggest that the web page is accurate.

Am I attempting the impossible?

Dan Bracuk
  • 20,699
  • 4
  • 26
  • 43

2 Answers2

5

Your correct that right now you cannot rename a column in this fashion. The fastest way to do this today is to run a CREATE TABLE AS SELECT (CTAS) operation. Your statement would look something like this:

CREATE TABLE Test.DimPatientNew AS SELECT City [town], <all other columns> FROM Test.DimPatient;

You can add your feedback to our public feedback forum here: https://feedback.azure.com/forums/307516-sql-data-warehouse

Rename a column request: https://feedback.azure.com/forums/307516/suggestions/18434083

Matt Usher
  • 1,325
  • 6
  • 10
  • Thank you for that. I added a request to enable Intellisense, https://feedback.azure.com/forums/307516-sql-data-warehouse/suggestions/33728026-enable-intellisense-with-ssms. I was surprised nobody had done it before. – Dan Bracuk Mar 23 '18 at 11:50
  • Thanks Dan. We appreciate the feedback and take your input very seriously. – Matt Usher Mar 23 '18 at 15:34
2

sp_rename and column is now supported in Azure Synapse Analytics. So you can use:

sp_rename '[schema].[table].[old_column]', '[new_column]' , 'COLUMN';

Ref: link

wBob
  • 13,710
  • 3
  • 20
  • 37
Daniel Bonetti
  • 2,306
  • 2
  • 24
  • 33