Rename all columns in the table, removing spaces

Question

I have a table that I imported from a very large CSV that has over 100 columns. I just noticed that they imported with spaces in the column names.

Is there a way to rename all columns and remove spaces?

Answer 1

The following query will remove all spaces from column names containing spaces in your_table in your_database . You can replace the desired values.

 SELECT CONCAT( 'ALTER TABLE ', C.TABLE_NAME, ' CHANGE `', C.COLUMN_NAME, '` ', REPLACE(C.COLUMN_NAME, ' ', ''), ' ', C.DATA_TYPE, ';' ) FROM INFORMATION_SCHEMA.COLUMNS C WHERE TABLE_SCHEMA = 'your_database' AND TABLE_NAME = 'your_table' AND C.COLUMN_NAME LIKE '% %'; 

Pay close attention to the back windows that surround the column name. This will output a set of ALTER TABLE statements that look like this:

 ALTER TABLE your_table CHANGE `Old Column Name` OldColumnName VARCHAR; 

Answer 2

Try this system procedure:

 EXEC sp_RENAME 'TableName.OldName' , 'NewName', 'COLUMN'