I messed up renaming a column - 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)

I messed up renaming a column

Author

Topic

triton45
Starting Member

16 Posts

Posted - 2008-03-04 : 14:16:08

the new column name is "mill_roll_log.[data_source_old]". How do I rename this column??

Yes, the table name is "mill_roll_log"

Andy

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-03-04 : 14:19:00

Use the sp_rename stored procedure.

CODO ERGO SUM

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-04 : 14:21:02

sp_rename 'mill_roll_log.[data_source_old]', 'my new column name'

E 12°55'05.25"
N 56°04'39.16"

triton45
Starting Member

16 Posts

Posted - 2008-03-04 : 14:41:13

quote:

sp_rename 'mill_roll_log.[data_source_old]', 'my new column name'

The table name = "mill_roll_log"
the column name = "mill_roll_log.[data_source_old]"

In a select this would be:
select mill_roll_log.mill_roll_log.[data_source_old]
from mill_roll_log

exec sp_rename 'mill_roll_log.mill_roll_log.[data_source_old]', 'data_source_2'
gives me this error:
No item by the name of 'mill_roll_log.mill_roll_log.[data_source_old]' could be found in the current database 'x', given that @itemtype was input as '(null)'.

SELECT name
FROM syscolumns
where object_Name(id) = 'mill_roll_log'

returns:
name
-------------------------
...
mill_roll_log.[data_source_old]
...

Thanks,
Andy

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-03-05 : 03:51:37

Have you looked at sp_rename in sql server help file?

sp_rename 'mill_roll_log.old_column', 'my new column name','COLUMN'

Madhivanan

Failing to plan is Planning to fail

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2008-03-05 : 07:43:08

quote:
Originally posted by triton45

quote:

sp_rename 'mill_roll_log.[data_source_old]', 'my new column name'

The table name = "mill_roll_log"
the column name = "mill_roll_log.[data_source_old]"

In a select this would be:
select mill_roll_log.mill_roll_log.[data_source_old]
from mill_roll_log

exec sp_rename 'mill_roll_log.mill_roll_log.[data_source_old]', 'data_source_2'
gives me this error:
No item by the name of 'mill_roll_log.mill_roll_log.[data_source_old]' could be found in the current database 'x', given that @itemtype was input as '(null)'.

SELECT name
FROM syscolumns
where object_Name(id) = 'mill_roll_log'

returns:
name
-------------------------
...
mill_roll_log.[data_source_old]
...

Thanks,
Andy

So you included the table in IN the column name.

Do as madhivan suggested, otherwise you have to wrap the WHOLE column in brackets.

mill_roll_log.[mill_roll_log.[data_source_old]]

Poor planning on your part does not constitute an emergency on my part.

triton45
Starting Member

16 Posts

Posted - 2008-03-05 : 10:03:45

quote:
So you included the table in IN the column name.

Unfortunately, yes

exec sp_rename 'mill_roll_log.[mill_roll_log.[data_source_old]]', 'data_source_old','COLUMN'

Server: Msg 15253, Level 11, State 1, Procedure sp_rename, Line 108
Syntax error parsing SQL identifier 'mill_roll_log.[mill_roll_log.[data_source_old]]'.

exec sp_rename 'mill_roll_log.data_source_old', 'data_source_old','COLUMN'

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.

I appreciate the suggestions.

Andy

Do as madhivan suggested, otherwise you have to wrap the WHOLE column in brackets.

mill_roll_log.[mill_roll_log.[data_source_old]]

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-03-05 : 10:12:50

try this:

exec sp_rename 'mill_roll_log."mill_roll_log.[data_source_old]"', 'data_source_old','COLUMN'

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-03-05 : 10:13:38

exec sp_rename 'mill_roll_log.[mill_roll_log.data_source_old]', 'data_source_old','COLUMN'

Em

triton45
Starting Member

16 Posts

Posted - 2008-03-05 : 11:14:54

quote:

exec sp_rename 'mill_roll_log."mill_roll_log.[data_source_old]"', 'data_source_old','COLUMN'

Worked perfectly. Thanks

Andy

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources