Today, I had a nice challenge, how to bulk rename column names in a database. The issue was I needed to move few tables from development environment to production environment and since some of the tables were imported from other environments, several column names needed to be standardized before the transfer.
To better illustrate this challenge, let’s look at an example of this;
Column names right now: dbo.date
dbo.user
Column names should be: dbo.create_date
dbo.create_user_id
SOLUTION:
There are several ways to accomplish this task but below are two that I prefer use.
The scripts below create SQL commands to rename the columns and all you have to do
is execute the result set:
1. Method 1:
SELECT ‘EXEC sp_rename ”’
+ OBJECT_NAME (c.object_id)+ ‘.’
+ c.name+ ”’, ”’
+ REPLACE (‘New_Column_Name’, ‘ ‘, ”)
+ ”’, ”COLUMN”’
FROM sys.columns c
INNER JOIN sys.objects o
ON (o.object_id = c.object_id) AND (o.is_ms_shipped = 0)
WHERE c.name LIKE ‘%Old_Column_Name%’ AND o.type_desc NOT LIKE ‘%VIEW%’
2.Method 2:
SELECT ‘EXEC sp_rename ”[‘
+ t.name+ ‘].[‘
+ c.name+ ‘]” , ”’
+ REPLACE (‘New_Column_Name’, ‘ ‘, ‘‘)
+ ”’, ”COLUMN”’
FROM sys.schemas s
INNER JOIN sys.tables t
ON s.schema_id = t.schema_id
AND t.is_ms_shipped = 0
AND s.name <> ‘SYS’
INNER JOIN sys.columns c ON t.object_id = c.object_id
WHERE c.name LIKE ‘%Old_Column_Name%’
And here is a snapshot of the result of the query:
