Technical Article

Modify columns' name in batches

,

First,we find all tables which contain given column'name from current database.Lastly,we can
modify columns' name in batches by cursor mechanism.Please change @value variable into your value.

use dbname
go
--Query all tables which contain given column'name from current database
create table #temp(id int,name sysname)
insert #temp
select a.id,a.name
from dbo.sysobjects a inner join (
select id
from dbo.syscolumns
where name='Sys_OrganizeCode') b on a.id=b.id
where a.xtype='u'
go
--by cursor
declare @name sysname
declare test_cur cursor for select name from #temp
open test_cur
fetch next from test_cur into @name
while @@fetch_status = 0
begin
--Modify columns' name
declare @sql varchar(50)
set @sql=@name+'.Sys_OrganizeCode'
EXEC sp_rename @sql, '@value', 'COLUMN'
fetch next from test_cur into @name
end
drop table #temp
close test_cur
deallocate test_cur
go

Rate

3 (1)

You rated this post out of 5. Change rating

Share

Share

Rate

3 (1)

You rated this post out of 5. Change rating