rename a column in all the tables - SQL

Question

I need to rename a column in all tables in my database. so a column 'OldColumn' has to be renamed to 'NewColumn' in all the tables

I could get list of tables that has this column using this query:

SELECT TABLE_NAME 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE COLUMN_NAME= <Column Name>

But how can i rename it in all tables as simple as possible and don't have to write a cursor?

Answer 1

Of course you don't need a cursor for this. You can use sys.columns and sys.objects to generate dynamic sql. Then simply execute it. Once you are satisfied the dynamic sql is what you want feel free to uncomment the last line.

----BE WARNED!!!!---- If you change column names your views, stored procedures, functions etc will all be broken.

declare @CurrentColumnName sysname = 'asdf'
    , @NewColumnName sysname = 'qwer'
    , @SQL nvarchar(MAX) = ''

select @SQL = @SQL + 'EXEC sp_rename ''' + o.name + '.' + c.name + ''', ''' + @NewColumnName + ''', ''COLUMN'';'
from sys.columns c
join sys.objects o on o.object_id = c.object_id
where c.name = @CurrentColumnName

select @SQL

--exec sp_executesql @sql