sp_rename - SQL Server Forums

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

SQL Server Administration (2000)

sp_rename

Author

Topic

X002548
Not Just a Number

15586 Posts

Posted - 2006-08-08 : 17:11:28

OK, so a third party vendor decides to name a column in their tables [function]. Now you wouldn't believe it if I told you, but bcp throws up all over this. I thought itt was the format card...so I figured I'd use sp_rename in the sproc like


		SET @FQColumn = 'dbo.'+@TABLE_NAME+'.Function' 
		IF EXISTS (SELECT * 
			     FROM INFORMATION_SCHEMA.Columns
			    WHERE TABLE_SCHEMA = @TABLE_SCHEMA
			      AND TABLE_NAME = @TABLE_NAME
			      AND COLUMN_NAME = 'Function')
		  BEGIN
			BEGIN TRAN
			EXECUTE sp_rename @FQColumn, N'FunctionX', 'COLUMN'
			COMMIT
		  END

Well t-sql doesn't like that either and I get

Server: Msg 15248, Level 11, State 1, Procedure sp_rename, Line 163
Either the parameter @objname is ambiguous or the claimed @objtype (COLUMN) is wrong.

But then I see that column is renamed...so it must be an error when I try to rename it back in this part



		IF EXISTS (SELECT * 
			     FROM INFORMATION_SCHEMA.Columns
			    WHERE TABLE_SCHEMA = @TABLE_SCHEMA
			      AND TABLE_NAME = @TABLE_NAME
			      AND COLUMN_NAME = 'FunctionX')
		  BEGIN
			BEGIN TRAN
			EXECUTE sp_rename @FQColumn, N'Function', 'COLUMN'
			COMMIT
		  END

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

X002548
Not Just a Number

15586 Posts

Posted - 2006-08-08 : 17:17:01

I tried this and still no luck

EXECUTE sp_rename @FQColumn, N'[Function]', 'COLUMN'

f'n venders

who names their columns as reserved words?

T-3 Days till vakay!

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-08-08 : 17:28:50

This worked OK from me.


create table xxx ( MyCol int )

EXECUTE sp_rename 'dbo.xxx.Mycol', N'Function', 'COLUMN'

Results:


Caution: Changing any part of an object name could break scripts and stored procedures.
The COLUMN was renamed to 'Function'.

CODO ERGO SUM

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-08-09 : 03:51:26

I think the problem is with the "dbo." part. Try this:

SET @FQColumn = @TABLE_NAME+'.Function'
IF EXISTS (SELECT * 
			     FROM INFORMATION_SCHEMA.Columns
			    WHERE TABLE_SCHEMA = @TABLE_SCHEMA
			      AND TABLE_NAME = @TABLE_NAME
			      AND COLUMN_NAME = 'Function')
		  BEGIN
			BEGIN TRAN
			EXECUTE sp_rename @FQColumn, N'FunctionX', 'COLUMN'
			COMMIT
		  END

....or maybe the table is not owned by dbo

Harsh Athalye
India.
"Nothing is Impossible"

Kristen
Test

22859 Posts

Posted - 2006-08-09 : 13:32:07

[code]
SET @FQColumn = 'dbo.'+@TABLE_NAME+'.Function'
IF EXISTS (SELECT *
FROM INFORMATION_SCHEMA.Columns
WHERE TABLE_SCHEMA = @TABLE_SCHEMA
AND TABLE_NAME = @TABLE_NAME
AND COLUMN_NAME = 'Function')
[/code]
Is it possible that the table is NOT owned by DBO?

Kristen

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-08-09 : 13:58:07

>>who names their columns as reserved words?
I've got no problem with that. Just ask my 3 kids: TakeOutTheTrash, MowTheLawn, and GetYourHandOutOfYourPants