How do I Change SQL Server column names in T-SQL?

Question

I have a table with 600+ columns imported from a CSV file with special chars % _ - in the column names. Is there a way to change the column names to remove these special chars?

The code can be in T-SQL.

Answer 1

sp_rename?

EXEC sp_rename 'table.[Oh%Dear]', 'OhDear', 'COLUMN';

Worked example (was not sure about [ ] in sp_rename)

CREATE TABLE foo ([Oh%Dear] int)

EXEC sp_rename 'foo.[Oh%Dear]', 'OhDear', 'COLUMN'

EXEC sys.sp_help 'foo'

Answer 2

You can query INFORMATION_SCHEMA.COLUMNS and generate sp_rename scripts to rename the columns.

SELECT 'EXEC sp_rename ''' + TABLE_NAME + '.' + QUOTENAME(COLUMN_NAME) + ''', ''' + REPLACE(COLUMN_NAME, '%', '') + ''', ''COLUMN''; '
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME LIKE '%[%]%'

Here's a permalink to an actual runnable example