Rename Column in Azure Data Warehouse

Question

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?

Frei

Hey Dan. Out of curiosity, why are you attempting to rename your column? – Azeria

Answer 1

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

Kava

Thank you for that. I added a request to enable Intellisense, feedback.azure.com/forums/307516-sql-data-warehouse/suggestions/…. I was surprised nobody had done it before. – Frei

Thanks Dan. We appreciate the feedback and take your input very seriously. – Kava

Answer 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

Castor