The project I am currently working on is a port from a horribly over-engineered Oracle system to a much more manageable SQL Server system. One of the things we needed to do was rename a lot of fields to something more usable. For instance, every table had a field titled "Ended" but we felt it was easier to think of that record as "Deleted". Rather than go through every table (and it wasn't in every table, meaning you needed to manually check each one to verify), I opted a scripting solution.

Below is a T-SQL script which will generate the sp_rename scripts for all the tables where the target field is found. A job which would have taken hours and been prone to errors is now a simple task taking a minute.

DECLARE @OldFieldName VARCHAR(50), @NewFieldName VARCHAR(50)

SET @OldFieldName = 'SomeOldFieldName'
SET @NewFieldName = 'MyNewFieldName'

select 'sp_RENAME ' + CHAR(39) + 'OFC.' + TABLE_NAME + '.' + @OldFieldName + CHAR(39) + ', ' + CHAR(39) + @NewFieldName + CHAR(39) + ',' + CHAR(39) + 'COLUMN'  + CHAR(39) + CHAR(10) + 'GO' + CHAR(10)
from INFORMATION_SCHEMA.COLUMNS 
WHERE COLUMN_NAME = @OldFieldName
ORDER BY TABLE_NAME

The script above will generate the T-SQL code to rename any field SomeOldFieldName to MyNewFieldName. Run it, and then run the generated script.

Given this and CodeSmith, can you tell I enjoy scripting work?