Search code examples
sqlsql-serverrenamesql-delete

Delete column in SQL that contains dots and squared brackets

How can one delete a column name [database].[dbo].[my_table].[col_name] in SQL server, that is a column name with dots and squared brackets. In other words, it is the column name that I wanted but prefixed by the database name and shema. I tried many combinations based on internet e.g. here but didn't succeed. Thank you.

Solution

  • I don't understand if you want to rename this column or drop it, but here is how to do both

    CREATE TABLE JustTest(
  Col1 INT,
  [[database]].[dbo]].[my_table]].[col_name]]] INT
);

-- To rename the column use this
EXEC sp_rename 'JustTest.[[database]].[dbo]].[my_table]].[col_name]]]', 
               'NewName', 
               'COLUMN';

-- If the table is TempTable use this
EXEC tempdb.sys.sp_rename N'#TMP.[[database]].[dbo]].[my_table]].[col_name]]]',
                N'NewName',
                N'COLUMN';

-- To drop it use this
ALTER TABLE JustTest DROP COLUMN [[database]].[dbo]].[my_table]].[col_name]]];