Removing quotes added to column names from Excel i

Question

I've noticed that when I use SSMS to import an Excel spreadsheet into SQL Server quotation marks are added. I've read somewhere that for whatever reason it's necessary for Excel to do this. Once in SQL Server, these quotes around the column names are useless and I'd like to have a programmatic way to remove them. The closest thing, which doesn't work, that I have tried to make is EXEC sp_rename 'Table.["withquotes"]', NewColumnName, 'replace(Table.["withquotes",'"','']. I'd like to loop through all of the column names in a table and use the replace function wherever a those column names contain quotation marks. Is there a typical, idiomatic way to do this?

Answer 1

I believe this should help...

DECLARE @tbl sysname, @col sysname
DECLARE @cmd nvarchar(max)

DECLARE cCol CURSOR FOR
    SELECT TABLE_NAME, COLUMN_NAME
    FROM INFORMATION_SCHEMA.COLUMNS 
    WHERE COLUMN_NAME LIKE '"%"'

OPEN cCol
FETCH NEXT FROM cCol INTO @tbl, @col
WHILE @@fetch_status = 0
BEGIN
  SET @cmd = 
    N'EXEC sp_rename ''[' + @tbl + '].[' + @col + ']'', ' + 
    '''' + REPLACE(@col, '"', '') + N''', ''COLUMN''' 

  --PRINT @cmd

  EXEC sp_executeSQL @cmd

  FETCH NEXT FROM cCol INTO @tbl, @col
END

CLOSE cCol 
DEALLOCATE cCol

Answer 2

Just for the info, I had errors with the procedure of OzrenTkalcecKrznaric. After searching, it was due to absence of schema name. So here is my version, updated to include that schema name:

DECLARE @tbl sysname, @col sysname, @sch sysname
DECLARE @cmd nvarchar(max)

DECLARE cCol CURSOR FOR
    SELECT TABLE_NAME, COLUMN_NAME, TABLE_SCHEMA
    FROM INFORMATION_SCHEMA.COLUMNS 
    WHERE COLUMN_NAME LIKE '"%"' 

OPEN cCol
FETCH NEXT FROM cCol INTO @tbl, @col, @sch
WHILE @@fetch_status = 0
BEGIN
  SET @cmd = 
    N'EXEC sp_rename ''[' + @sch + '].[' + @tbl + '].[' + @col + ']'', ' + 
    '''' + REPLACE(@col, '"', '') + N''', ''COLUMN''' 

  --PRINT @cmd

  EXEC sp_executeSQL @cmd

  FETCH NEXT FROM cCol INTO @tbl, @col, @sch
END

CLOSE cCol 
DEALLOCATE cCol

---

One can also generate the statements, to be then copied, pasted and executed:

USE myDb
select 'Exec sp_rename ''' + QuoteName(Schema_Name(tables.schema_id)) + '.' + QuoteName(tables.name) + '.' + QuoteName(columns.name) + '''' +
   ',''' + REPLACE ( columns.name , '"' , '') + ''', ''COLUMN'''
from sys.columns
join sys.tables on columns.object_id = tables.object_id
join sys.schemas on tables.schema_id = schemas.schema_id
where sys.columns.name like '"%"' AND sys.schemas.name =  'mySchema'

(replace myDb and mySchema by your values)