Rename all columns in a table removing whitespace

2018-06-30 22:06:48

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

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

The following query will remove all whitespace from column names containing any white space in the table your_table in the database your_database . You can replace with the values you need.

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 very close attention to the backticks which surround the column name. This will output a set of ALTER TABLE statements which look like the following:

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

试试这个系统过程：

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

链接地址: http://www.djcxy.com/p/86246.html

上一篇: 没有被字典引用的字符串？

下一篇: 重命名表中删除空格的所有列