How to bulk rename column names in a database in SQL Server

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:

Column names should be:       dbo.create_date


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)+ ‘.’
+ ”’, ”’
+ 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 LIKE ‘%Old_Column_Name%’ AND o.type_desc NOT LIKE ‘%VIEW%’

2.Method 2:

SELECT   ‘EXEC sp_rename ”[‘
+ ‘].[‘
+ ‘]” , ”’
+ 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 <> ‘SYS’
       INNER JOIN sys.columns c ON t.object_id = c.object_id
 WHERE LIKE ‘%Old_Column_Name%’

And here is a snapshot of the result of the query:

bulk rename column name on all tables in the database in SQL Server

Scroll to top