error when using (trying to use) sp_rename

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 2000 Forums

Transact-SQL (2000)

error when using (trying to use) sp_rename

Author

Topic

storemike
Starting Member

10 Posts

Posted - 2003-10-14 : 16:34:36

Hello,

I'm trying to change all column names to uppercase via a query to eliminate the chance of me forgetting a letter or two. when I run the following code, I get an error of

Server: Msg 2705, Level 16, State 1, Procedure sp_rename, Line 397
Column names in each table must be unique. Column name 'ID' in table '[dbo].[bubba]' is specified more than once.
Caution: Changing any part of an object name could break scripts and stored procedures.

Any suggestions to get around this? I'm new to Transact-SQL, so it may be something simple I'm missing.

The table name is "bubba" in a database called "test" on SQL Server 7.

Thanks,
Mike

################ SQL follows ##################

declare @colname varchar(255)
declare @uppercasecolname varchar(255)
declare o_curs cursor for
select COLUMN_NAME from information_schema.columns WHERE TABLE_NAME ='bubba'

open o_curs

FETCH NEXT FROM o_curs INTO @colname

while (@@fetch_status <> -1)
begin
print upper(@colname)
set @uppercasecolname=upper(@colname)
EXEC('EXEC sp_rename ''bubba.'+@colname+''', '''+@uppercasecolname+''', ''COLUMN''')

FETCH NEXT FROM o_curs INTO @colname
end
close o_curs

############## SQL end #################

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-10-14 : 16:45:20

sp_rename does not allow you to rename it to the same thing. I realize that you are changing it to upper case, but to sp_rename, ID is the same thing as id. So you could rename it to something else and then rename it again.

I will provide a solution that does this. Give me a couple.

Tara

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-10-14 : 16:48:53

Here ya go:



declare @colname varchar(255)
declare @uppercasecolname varchar(255)
declare @sql varchar(8000)
declare o_curs cursor for 
select COLUMN_NAME from information_schema.columns WHERE TABLE_NAME ='bubba'

open o_curs

FETCH NEXT FROM o_curs INTO @colname

while (@@fetch_status <> -1)
begin
set @uppercasecolname=upper(@colname) + 'A'

EXEC ('EXEC sp_rename ''bubba.'+@colname+''', '''+@uppercasecolname+''', ''COLUMN''')

SELECT @SQL = 'EXEC sp_rename ''bubba.'+@colname+'A'', '''+SUBSTRING(@uppercasecolname, 1, LEN(@uppercasecolname) - 1)+''', ''COLUMN'''

EXEC (@SQL)

FETCH NEXT FROM o_curs INTO @colname
end

close o_curs
deallocate o_curs

It adds A to the end of the column the first time it renames it. The second time, it removes the A and now it is upper case.

Tara

storemike
Starting Member

10 Posts

Posted - 2003-10-14 : 18:20:41

Tara,

thanks a bunch! It works perfect.

Mike

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources