Renaming a column name on multile databases

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Renaming a column name on multile databases

Author

Topic

Sigal
Starting Member

11 Posts

Posted - 2008-03-03 : 10:16:13

Hi there,
Can anyone tell me how to run sp_rename to rename a column name on multile databases? I have 150 databases to update.

thanks very much

Abu-Dina
Posting Yak Master

206 Posts

Posted - 2008-03-03 : 10:30:14

[code]

set nocount on

-- DDL and some sample data

declare @DatabaseName varchar(255), @DatabaseId smallint

declare @Databases table
(
DatabaseId smallint identity(1, 1),
DatabaseName varchar(255)
)

insert into @Databases
(
DatabaseName
)
select 'DB-1' union all
select 'DB-2' union all
select 'DB-3'

set @DatabaseId = (select min(DatabaseId) from @Databases)
set @DatabaseName = (select DatabaseName from @Databases where DatabaseId = @DatabaseId)

while @DatabaseName is not null
begin
exec ('sp_rename ''' + @DatabaseName + '..someTable.someColumn'', ' + '''newColumnName'', ''COLUMN'';')

set @DatabaseId = (select min(DatabaseId) from @Databases where DatabaseId > @DatabaseId)
set @DatabaseName = (select DatabaseName from @Databases where DatabaseId = @DatabaseId)
end

set nocount off[/code]

Sigal
Starting Member

11 Posts

Posted - 2008-03-03 : 11:27:27

Thanks very much for your quick response but I don't think I've explained myself properly. I was looking for a script that would automatically run through all my databases as I don't want to name each one of the 150 separately.

Abu-Dina
Posting Yak Master

206 Posts

Posted - 2008-03-03 : 11:53:49

The WHILE loop will do that for you as long as the table variable is populated with the names of your 150 databases. How you poplate the table variable is entirely up to you.

select * from master..sysdatabases

shoudl return all databases on a server. How you find your 150 databases I don't know!

Good luck.

Sigal
Starting Member

11 Posts

Posted - 2008-03-03 : 12:15:02

Hi, I've run this script but getting errors:
============================================
set nocount on

-- DDL and some sample data

declare @DatabaseName varchar(255), @DatabaseId smallint

declare @Databases table
(
DatabaseId smallint identity(1, 1),
DatabaseName varchar(255)
)

insert into @Databases
(
DatabaseName
)

select name from master..sysdatabases

set @DatabaseId = (select min(DatabaseId) from @Databases)
set @DatabaseName = (select DatabaseName from @Databases where DatabaseId = @DatabaseId)

while @DatabaseName is not null
begin
exec ('sp_rename ''' + @DatabaseName + 'SentTo.BoiunceCategory'', ' + '''BounceCategory'', ''COLUMN'';')

set @DatabaseId = (select min(DatabaseId) from @Databases where DatabaseId > @DatabaseId)
set @DatabaseName = (select DatabaseName from @Databases where DatabaseId = @DatabaseId)
end

set nocount off
============================================
I'm getting: Either the parameter @objname is ambiguous or the claimed @objtype (COLUMN) is wrong.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-03-03 : 13:02:52

You didn't copy his code correctly ....

exec ('sp_rename ''' + @DatabaseName + '..SentTo.BoiunceCategory'', ' + '''BounceCategory'', ''COLUMN'';')

- Jeff
http://weblogs.sqlteam.com/JeffS

Sigal
Starting Member

11 Posts

Posted - 2008-03-04 : 05:58:13

Thanks, but still getting the same error:
"Either the parameter @objname is ambiguous or the claimed @objtype (COLUMN) is wrong."

Abu-Dina
Posting Yak Master

206 Posts

Posted - 2008-03-04 : 07:00:05

quote:
Originally posted by Sigal

Thanks, but still getting the same error:
"Either the parameter @objname is ambiguous or the claimed @objtype (COLUMN) is wrong."

Spelling error perhaps?

exec ('sp_rename ''' + @DatabaseName + 'SentTo.BoiunceCategory'', ' + '''BounceCategory'', ''COLUMN'';')

Sigal
Starting Member

11 Posts

Posted - 2008-03-04 : 07:18:32

That is why i need to run the script, this spelling mistake appears in 150 db and I would like to correct it in a batch.
I suspect this error is occurring because of the few databases that do not have this column, is there a way to check if the column exists before executing the procedure?

Abu-Dina
Posting Yak Master

206 Posts

Posted - 2008-03-04 : 07:39:44

quote:
Originally posted by Sigal

That is why i need to run the script, this spelling mistake appears in 150 db and I would like to correct it in a batch.
I suspect this error is occurring because of the few databases that do not have this column, is there a way to check if the column exists before executing the procedure?

Something like this:


set @DatabaseId = (select min(DatabaseId) from @Databases)
set @DatabaseName = (select DatabaseName from @Databases where DatabaseId = @DatabaseId)

while @DatabaseName is not null
	begin
		exec('if exists (select * from ' + @DatabaseName + '.INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = ''SentTo'' and COLUMN_NAME = ''BoiunceCategory'')
		begin
			 sp_rename ''' + @DatabaseName + '..someTable.someColumn'', ' + '''newColumnName'', ''COLUMN'' 
		end ;')

	set @DatabaseId = (select min(DatabaseId) from @Databases where DatabaseId > @DatabaseId)
	set @DatabaseName = (select DatabaseName from @Databases where DatabaseId = @DatabaseId)
end

Sigal
Starting Member

11 Posts

Posted - 2008-03-04 : 08:34:51

Hiya, this doesn't seem to work either but I just found a very simple solution which seems to work perfectly:

sp_msforeachdb @command1= 'USE ?;
EXEC sp_rename "SentTo.BoiunceCategory", BounceCategory'

Thank you very much for all your help.