Renaming all columns in a table quickly

Recently imported a table where the column names where quoted using double quotes which then became the table names , So a column name would be “Airline” instead of Airline. Here is a simple script to rename all the columns in all tables within a database.

SELECT '[' + TABLE_SCHEMA + ']' + '.[' + TABLE_NAME + '].[' + COLUMN_NAME + ']' AS oldcolname
    ,replace(COLUMN_NAME, '"', '') AS Newcolname
INTO #store
FROM INFORMATION_SCHEMA.COLUMNs

ALTER TABLE #store ADD id INT identity (
    1
    ,1
    )

SELECT *
FROM #store

DECLARE @id INT = 1

WHILE @id <= 260
BEGIN
    DECLARE @tablename VARCHAR(1000) = (
            SELECT oldcolname
            FROM #store
            WHERE id = @id
            )
    DECLARE @tablename1 VARCHAR(1000) = (
            SELECT Newcolname
            FROM #store
            WHERE id = @id
            )

    EXEC sp_rename @tablename
        ,@tablename1
        ,'COLUMN'

    SET @id = @id + 1
END

Please Consider Subscribing

Leave a Reply