Rename a column name (containing dot in it) of a table

Gulhasan.Siddiquee 101

I want to change a column name of a table . But issue is that column name is containing dot in it . which is creating issue while I am executing below statement.:-

I can not drop the table as it contains huge and important data

expecting Column name = mobileappperformanceappid_app-perf-app-name

current column name = mobileappperformanceappid.app-perf-app-name

EXEC sp_rename 'source.tbalename.mobileappperformanceappid.app-perf-app-name' , 'mobileappperformanceappid_app-perf-app-name','COLUMN';

Andrew S 0 Reputation points

2023-03-17T10:31:43.37+00:00

You can escape the column with [Mycolumn.Name]

Gulhasan.Siddiquee 101

EXEC sp_rename '[source.tablename].[mobileappperformanceappid.app-perf-app-name]' , 'mobileappperformanceappid_app-perf-app-name','COLUMN';
EXEC sp_rename '[source.tablename.mobileappperformanceappid.app-perf-app-name]' , 'mobileappperformanceappid_app-perf-app-name','COLUMN';
EXEC sp_rename '[source].[tablename].[mobileappperformanceappid.app-perf-app-name]' , 'mobileappperformanceappid_app-perf-app-name','COLUMN';
EXEC sp_rename 'source.tablename.[mobileappperformanceappid.app-perf-app-name]' , 'mobileappperformanceappid_app-perf-app-name','COLUMN';
EXEC sp_rename 'source.tablename.mobileappperformanceappid.app-perf-app-name' , 'mobileappperformanceappid_app-perf-app-name','COLUMN';

Gulhasan.Siddiquee 101

I tries all these combination and none of it was successfull . Getting error everytime

EXEC sp_rename '[source.tablename].[mobileappperformanceappid.app-perf-app-name]' , 'mobileappperformanceappid_app-perf-app-name','COLUMN';
EXEC sp_rename '[source.tablename.mobileappperformanceappid.app-perf-app-name]' , 'mobileappperformanceappid_app-perf-app-name','COLUMN';
EXEC sp_rename '[source].[tablename].[mobileappperformanceappid.app-perf-app-name]' , 'mobileappperformanceappid_app-perf-app-name','COLUMN';
EXEC sp_rename 'source.tablename.[mobileappperformanceappid.app-perf-app-name]' , 'mobileappperformanceappid_app-perf-app-name','COLUMN';
EXEC sp_rename 'source.tablename.mobileappperformanceappid.app-perf-app-name' , 'mobileappperformanceappid_app-perf-app-name','COLUMN';

Andrew S 0 Reputation points

2023-03-17T11:07:48.4533333+00:00

That's odd. The pattern should be 'SchemaName.TableName.ColumnName' or 'TableName.ColumnName' and USE DatabaseName command before.

sys.objects may provide some information on the name it is using.

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

You might also be able to get a valid result by renaming the column in SSMS table designer and scripting the results.

An error message might help diagnose further.
Gulhasan.Siddiquee 101 Reputation points

2023-03-17T12:36:45.8833333+00:00

Started executing query at Line 5

Msg 103031, Level 16, State 1, Line 1
Parse error at line: 1, column: 32: Expected but did not find a closing square bracket after the identifier [mobileappperformanceappid.

Total execution time: 00:00:00.466

Viorel 112.5K

What error did you get in this case?

EXEC sp_rename '[tablename].[mobileappperformanceappid.app-perf-app-name]' , 'mobileappperformanceappid_app-perf-app-name', 'COLUMN'
-- or:
EXEC sp_rename '[schema].[tablename].[mobileappperformanceappid.app-perf-app-name]' , 'mobileappperformanceappid_app-perf-app-name', 'COLUMN'

ShaktiSingh-MSFT 13,516 Reputation points Microsoft Employee

2023-03-20T06:00:17.1533333+00:00

Hi @Gulhasan.Siddiquee ,

We have not heard back from you. Could you please reply to above comment by Viorel so that you would get further assistance on the question. Thank you.
Gulhasan.Siddiquee 101 Reputation points

2023-03-20T11:36:37.8966667+00:00

Capture.PNGI Have already mentioned about these scenarios in Andrews comment.

Here again attaching the screenshot for your reference.
ShaktiSingh-MSFT 13,516 Reputation points Microsoft Employee

2023-03-21T06:36:06.07+00:00

Hi @Gulhasan.Siddiquee ,

Thanks for the reply.

Since there are multiple tags added to the question, can you please confirm where are you executing the commands, Azure SQL Database or Azure Synapse or some other service?

Accepted answer

ShaktiSingh-MSFT 13,516 Reputation points Microsoft Employee

2023-04-04T12:42:40.71+00:00

Hi Gulhasan.Siddiquee •

Thanks for trying afresh the queries.

The problem looks strange, I tried to follow the same naming convention for the schema and tables and could execute commands successfully as shown below:

The issue in your Azure Data Studio looks strange and not expected. I would recommend you to please file a support ticket for deeper investigation and in case if you don't have a support plan, do let us know here so that we can check on other options to unblock you.

Thanks
Please sign in to rate this answer.
Gulhasan.Siddiquee 101 Reputation points

2023-04-05T06:18:22.3666667+00:00

Thanks @ShaktiSingh-MSFT We could not afford to wait so we took alternative methods and proceeding with project . But we would like to raise support ticket , so that in future we would able to do it if any such requirement come on the way. Thanks again for your continuous support.

ShaktiSingh-MSFT 13,516 Reputation points Microsoft Employee

2023-04-05T08:35:52.8033333+00:00

Hi Gulhasan.Siddiquee •

Could you send an email to AzCommunity@Microsoft.com with your Subscription ID and thread link to this post, and I will enable a one-time free support request for your subscription.

Please reference this forum thread in the subject: ATTN: SHAKTI - Rename a column name (containing dot in it) of a table. Regards, @ShaktiSingh-MSFT
Sign in to comment

3 additional answers

Bjoern Peters 8,781 Reputation points

2023-03-21T17:14:20.39+00:00
Hi Gulhasan,

Welcome to Q&A Forum; this is a great place to get support, answers, and tips.

Thank you for posting your query; I'll be more than glad to help you out.

I was able to change the name of your column ;-)

EXEC sp_rename 'dbo.demotable.[mobileappperformanceappid.app-perf-app-name]' , 'mobileappperformanceappid_app-perf-app-name','COLUMN';

You have to set only the column name in brackets.

That worked for my environment... SQL Server 2019 Developer (Ubuntu) should also work in your env.

I hope my answer is helpful to you,

Your

Bjoern Peters

If the reply was helpful, please upvote and/or accept it as an answer, as this helps others in the community with similar questions. Thanks!
Please sign in to rate this answer.
Gulhasan.Siddiquee 101 Reputation points

2023-03-22T06:58:17.78+00:00

Hi Bjoern,

Its not working for me . Here is screenshot. Capture.PNGy

My environment is Azure Data studio. Our actual requirement is to pass this line of code in Azure Devops pipeline.

It seems we do not have any option except create back up of data and then truncate the old table followed by creating new table with updated column name.

We wish to avoid it because data volume is huge , so its time consuming.
Sign in to comment
ShaktiSingh-MSFT 13,516 Reputation points Microsoft Employee

2023-03-28T05:52:28.4566667+00:00
Hi @Gulhasan.Siddiquee ,

Thanks for letting us know that you are working on Azure Data Studio.

As I understood, you are getting error in renaming a column of the table.

I have tried to create a dummy table with column name same as mentioned by you in the original question and then tried to rename it using below command and was able to do it successfully:

EXEC sp_rename 'table3.[mobileappperformanceappid.app-perf-app-name]', 'mobileappperformanceappid_app-perf-app-name', 'COLUMN'; GO

Could you please try above and let us know if it works. If not, let me know your table structure so that I may try to reproduce it. Thanks
Please sign in to rate this answer.
ShaktiSingh-MSFT 13,516 Reputation points Microsoft Employee

2023-03-29T05:15:00.0266667+00:00

Hi Gulhasan.Siddiquee •,

Following up to see if the above suggestion was helpful. If this answers your query, do click Accept Answer and Mark Helpful for the same. And, if you have any further query do let us know.

Gulhasan.Siddiquee 101 Reputation points

2023-04-03T12:41:38.56+00:00

Hi @ShaktiSingh-MSFT

It is working for you because you have not created table under any schema. Please refer attached screenshot Capture.PNG
Sign in to comment
ShaktiSingh-MSFT 13,516 Reputation points Microsoft Employee

2023-04-04T06:53:25.3433333+00:00

Hi Gulhasan.Siddiquee •, Please find below details of the query execution of table inside schema working fine:

Try to update Azure Data Studio and execute. If not working, kindly try to create a sample table and execute commands to check if the issue is with existing table or with new too. Thank you.
Please sign in to rate this answer.
Gulhasan.Siddiquee 101 Reputation points

2023-04-04T12:20:47.2233333+00:00

Created sample table and then tried - Not working

Updated Azure studio then created sample table and tried - Not working

And on original table also, it is not working.
Sign in to comment