Styjun

Unable to update the column_name column in information_schema.columns

I have a table named 'knd' in MS-SQL server. Now I want to alter the column names of all the columns in this table in this way:

for example, my column names in this table are: Fuel category, fuel type, end date, start date

I want to update these names to [Fuel category], [fuel type], [end date], [start date]. i.e column names must include [] and the updation should be done in one shot.

What I have tried:

update INFORMATION_SCHEMA.COLUMNS
Set COLUMN_NAME = CONCAT('[',COLUMN_NAME,']')
where TABLE_NAME = 'knd'

I get the below error:

Ad hoc updates to system catalogs are not allowed.

I tried to reconfigure with override as below, but didn't work:

exec sp_configure 'allow updates','1';
go
reconfigure with override
go

even if I have to use exec sp_rename, how can I do it for all columns in one shot. I believe using sp_rename requires more manual intervention as my column names might change tommorow .

Can someone please help to accomplish this?

First: This is a terrible idea, as everyone wrote in the comments. Adding square brackets to column names will only force you to refer to the columns with double square brackets - to refer to a column named [fuel type] you will have to write [[fuel type]]].

Second, You can't directly update system tables or the views that relies on them. Everything in the sys schema and in the INFORMATION_SCHEMA schema is readonly. To rename a column in a view, you must write an alter view statement, or use sp_rename. To rename a column in a table, you must write an alter table statement or use sp_rename.

That being said, it's best to first find all objects that depends on the column you want to rename, becuase renaming a column will not rename every reference to it, so you might break stuff when renaming.

You can query the built in table valued function sys.dm_sql_referencing_entities to get dependencies of an object in SQL Server.

Quotename does the needful in my case as suggested by @jeroen-mostert in one of the above comments!!.

Below is my simple code snippet to perform this for all the columns in my table set.

Select STUFF((SELECT N',' + QUOTENAME(C.COLUMN_NAME)
 FROM INFORMATION_SCHEMA.COLUMNS C
 WHERE C.TABLE_NAME = 'knd'
 FOR XML PATH(N''),TYPE).value(N'.',N'nvarchar(MAX)'),1,1,N'')

Result is as follows:

[Start Date_(MM-DD-YYYY)],[End Date_(MM-DD-YYYY)],[mode],[scac],[fuel category],[Fuel Type],[Base],[Escalator],[Surcharge],[FSC @ $3.2],[Step],[Co_ID]