Renaming Columns via Query Analyser

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

General SQL Server Forums

New to SQL Server Programming

Renaming Columns via Query Analyser

Author

Topic

flanz_g
Starting Member

22 Posts

Posted - 2006-12-19 : 10:40:17

Could somone advise on how to change Names of Columns in a table
via a script rather then Enterprise Manager. I have lots of columns (need to do it in bulk.) called like SUM(abc1...etc), which I can not change. This upsets the Business Objects because it thinks it needs a GROUP BY statement.
I know in MapBasic there is something like " Alter Table "tablename" ( rename Date Date_of_Day ) " Is there an equivalent for TRANSACT SQL?.
Please help, (and Happy Xmas!)
Gezza

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-19 : 10:49:35

ALTER TABLE ALTER COLUMN <YourColumnNameHere> ...

Read all about it in Books Online.

Peter Larsson
Helsingborg, Sweden

X002548
Not Just a Number

15586 Posts

Posted - 2006-12-19 : 12:43:43

BOL:


B. Rename a column
This example renames the contact title column in the customers table to title.

EXEC sp_rename 'customers.[contact title]', 'title', 'COLUMN'

However, you would be better served to bcp out the data, drop and recreate, then reload

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

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-12-19 : 12:54:08

quote:
Originally posted by flanz_g
I have lots of columns (need to do it in bulk.) called like SUM(abc1...etc), which I can not change. This upsets the Business Objects because it thinks it needs a GROUP BY statement.
Gezza

????

I am not sure if I want to hear more details about this or not, but I am awfully curious what the heck you are talking about here ....

- Jeff

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-12-19 : 22:19:37

1 When you use aggregate functions you should specify the alias name eg SUM(col) as Col
2 Post the query you used in CR

Madhivanan

Failing to plan is Planning to fail

flanz_g
Starting Member

22 Posts

Posted - 2006-12-22 : 08:48:35

Hi Brett,
"
B. Rename a column
This example renames the contact title column in the customers table to title.
a)EXEC sp_rename 'customers.[contact title]', 'title', 'COLUMN'

b)However, you would be better served to bcp out the data, drop and recreate, then reload ..."

I have done b) and it worked a treat! Thanks.
Gezza

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources