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

Subscribe