question

SrikanthYerramsettiAvanadeInc-5186 avatar image
0 Votes"
SrikanthYerramsettiAvanadeInc-5186 asked DavidSartoris-3027 answered

How to rename a column in Azure SQL Data warehouse?

Using below link we wrote a query to rename the column name in Azure SQL Data warehouse
https://docs.microsoft.com/en-us/sql/t-sql/statements/rename-transact-sql?view=aps-pdw-2016-au7#:~:text=Renames%20a%20user%2Dcreated%20table%2C%20a%20column%20in%20a%20user,(Azure%20SQL%20Database)%20statement.

rename object tablename column Col1 to Col2;

when we ran the above query it is throwing error message Incorrect Syntax near 'Column'

and also we ran a different query
Alter table tablename rename column Col1 to Col2;

the above query giving error message like Incorrect syntax near 'Rename'

azure-synapse-analytics
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

PRADEEPCHEEKATLA-MSFT avatar image
1 Vote"
PRADEEPCHEEKATLA-MSFT answered NageshA-8005 commented

Hello @SrikanthYerramsettiAvanadeInc-5186,

Welcome to the Microsoft Q&A platform.

sp_rename (Transact-SQL) - This feature is in Preview for columns in user tables.

Columns before Rename:

54190-image.png

The following example renames the MedallionID column in the tripoutput table to MID.

 EXEC sp_rename 'dbo.tripoutput.MedallionID', 'MID', 'COLUMN';

54338-image.png

Columns after Rename:

54387-image.png

Hope this helps. Do let us know if you any further queries.


  • Please accept an answer if correct. Original posters help the community find answers faster by identifying the correct answer. Here is how.

  • Want a reminder to come back and check responses? Here is how to subscribe to a notification.



image.png (52.1 KiB)
image.png (17.8 KiB)
image.png (48.3 KiB)
· 4
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

hello @PRADEEPCHEEKATLA-MSFT , When i try to rename the column, i'm getting the below error. My table is not in the default dbo schema.

EXEC sp_rename 'schemaName.tableName.OldColumnName', 'NewColumnName', 'COLUMN';

Error: Msg 15165, Level 16, State 1, Line 1
Could not find object 'tableName' or you do not have permission.

Also tried the below approach. But no luck.
RENAME OBJECT databaseName.schemaName.TableName COLUMN oldColumnName TO newColumnName ;

Error: Parse error at line: 6, column: 1: Incorrect syntax near 'COLUMN'.




1 Vote 1 ·

Hello @SrikanthYerramsettiAvanadeInc-5186,

Just checking in to see if the above answer helped. If this answers your query, do click “Accept Answer” and Up-Vote for the same. And, if you have any further query do let us know.

0 Votes 0 ·

Hello, @PRADEEPCHEEKATLA-MSFT ,

Can we use sp_rename to rename a column in table which is not in dbo schema? We have multiple different schemas and sp_rename is failing if we try to rename a table which is not in dbo?

1 Vote 1 ·

Hello @SrikanthYerramsettiAvanadeInc-5186,

Following up to see if the above suggestion was helpful. And, if you have any further query do let us know.

Take care & stay safe!

0 Votes 0 ·
abhishekpalsingh avatar image
0 Votes"
abhishekpalsingh answered

Is there any update on this issue?

I am also facing the same error while performing RENAME of a column -
Command : RENAME OBJECT databaseName.schemaName.TableName COLUMN oldColumnName TO newColumnName ;

Msg 103010, Level 16, State 1, Line 130
Parse error at line: 2, column: 46: Incorrect syntax near 'COLUMN'.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

DavidSartoris-3027 avatar image
0 Votes"
DavidSartoris-3027 answered

see https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-rename-transact-sql?view=sql-server-ver15

"In Azure Synapse Analytics, sp_rename is in Preview and can only be used to rename a COLUMN in a user object in the dbo schema."

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.