Refactor rename field in many database tables

Question

I am faced with renaming a field where the same fieldname is replicated across many tables.

Looking for a script or free/low cost tool which will go through all the tables and if fieldA exists, rename it to fieldB.

thanks

Answer 1

You can use SQL Server metadata tables to create dynamic sql statements for your purpose. For list of available tables you can sys.tables for list of tables and sys.columns for list of columns. using these tables you can create a table of sql statements. For executing dynamic sqls you need to sp_executesql stored procedure.

This is a sample code just to show how to use metadata tables and sp_executesql: And note that I used other metadata tables which I am more comfortable with. also you may use a cursor to run all the scripts returned by query.

CREATE Database TestDB

CREATE Table Table1 (Id int , fieldA varchar(50) )

Declare @update_query nvarchar(max)

select 
    @update_query = 'sp_rename ''' + t.TABLE_NAME + '.' + c.COLUMN_NAME + ''',''' + 'fieldB' + ''''
From INFORMATION_SCHEMA.COLUMNS c JOIN INFORMATION_SCHEMA.Tables t ON c.TABLE_CATALOG = t.TABLE_CATALOG AND c.TABLE_SCHEMA = t.TABLE_SCHEMA AND c.TABLE_NAME = t.TABLE_NAME
WHERE
    c.COLUMN_NAME = 'fieldA'

SELECT @update_query

EXEC sp_executesql @update_query