Automate renaming columns
This script finds and renames columns that begin with a digit, contains a space, or contains a hyphen, so that it is no longer necessary to put brackets around the column names. This is useful in situations such as when an application doesn't automatically put brackets around column names when passing queries to a SQL Server database. You can easily modify it to control what you replace and what you replace it with.
/* Rename columns that begin with a digit, contains a space, or contains a hyphen.
**-------------------------------------------------------------------------------------
** This renaming is useful in situations such as when an application doesn't put
** brackets around column names when passing queries to a SQL Server database.
**-------------------------------------------------------------------------------------
** Author: John D. Lambert, www.ElfInk.com
**-------------------------------------------------------------------------------------
** If you find this script helpful, please visit www.ElfInk.com and drill down to the
** SQL/Database stuff I have for sale. Items include humorous and professional
** t-shirts, mugs, hats, underwear, clocks, teddy bears, magnets, stickers, & much more...
**-------------------------------------------------------------------------------------*/
DECLARE Cur1 CURSOR FAST_FORWARD FOR
SELECT o.name tbl, c.name col
FROM sysobjects o INNER JOIN syscolumns c ON o.id = c.id
WHERE IsNumeric(Left(c.name,1)) = 1 --begins with a digit, prepend an underscore
OR CharIndex(' ',c.name) > 0 --contains a space, replace with underscore
OR CharIndex('-',c.name) > 0 --contains a hyphen, replace with underscore
DECLARE @tbl varchar(1000), @col varchar(1000), @sql nvarchar(4000)
OPEN Cur1
FETCH NEXT FROM Cur1 INTO @tbl, @col
WHILE @@fetch_status = 0
BEGIN
IF IsNumeric(Left(@col,1)) = 1
SET @sql = 'EXECUTE sp_rename ' + char(39) + @tbl + '.' + @col + char(39) + ', ' + char(39) + '_' + Replace(Replace(@col,' ','_'),'-','_') + char(39) + ', ' + char(39) + 'COLUMN' + char(39)
ELSE
SET @sql = 'EXECUTE sp_rename ' + char(39) + @tbl + '.' + @col + char(39) + ', ' + char(39) + Replace(Replace(@col,' ','_'),'-','_') + char(39) + ', ' + char(39) + 'COLUMN' + char(39)
PRINT @sql
EXECUTE sp_executesql @sql
FETCH NEXT FROM Cur1 INTO @tbl, @col
END
CLOSE Cur1
DEALLOCATE Cur1 
