473,322 Members | 1,431 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

SQL 2000 renaming columns in tables

19
This code works in query analyzer to rename one column in a table, can someone please give me the script to rename more than one column in a table and along with that, I need to change the data type (length) while I'm at it.

sp_rename 'medicaid.col001', 'id', 'COLUMN'
Mar 10 '07 #1
2 6781
bergy
89
This code works in query analyzer to rename one column in a table, can someone please give me the script to rename more than one column in a table and along with that, I need to change the data type (length) while I'm at it.

sp_rename 'medicaid.col001', 'id', 'COLUMN'
I usually rename and modify data types like so:

Change Datatypes:
ALTER TABLE table_name ALTER COLUMN some_column varchar(100);

Change Column Name:
ALTER TABLE table_name RENAME COLUMN old_name to new_name;

I know it's not all in one query to the DB but maybe you could make a procedure to take care of it? Or if you're doing this in an application it shouldn't matter.
Mar 11 '07 #2
kbaisch
19
I usually rename and modify data types like so:

Change Datatypes:
ALTER TABLE table_name ALTER COLUMN some_column varchar(100);

Change Column Name:
ALTER TABLE table_name RENAME COLUMN old_name to new_name;

I know it's not all in one query to the DB but maybe you could make a procedure to take care of it? Or if you're doing this in an application it shouldn't matter.
Thanks Bergy, I'll give it a try.
Mar 12 '07 #3

Sign in to post your reply or Sign up for a free account.

Similar topics

3
by: Tina Harris | last post by:
I ran the following query in Query Analyzer for a 7 column table. SELECT c.name,c.colid FROM syscolumns c WHERE c.id=925962375 ORDER BY c.colid The results were: I_CSD 1 X_STE_XML 2...
3
by: JB | last post by:
To anyone that is able to help.... What I am trying to do is this. I have two tables (Orders, and OrderDetails), and my question is on the order details. I would like to set up a stored...
1
by: Trevor Jackson | last post by:
Is there a limit on the number of columns that a table can have when you link to the table using Access 2000? Attempting to set up a link to an Oracle table that has 300+ columns - (reason for...
1
by: Don Leverton | last post by:
Hi Folks, I have been given a CD with approx 130 .xls files (bean-counters!) that I would like to import and merge to ONE table (tblTradeshow). The XL files are *similarly*, but not...
6
by: Peter Frost | last post by:
Please help I don't know if this is possible but what I would really like to do is to use On Error Goto to capture the code that is being executed when an error occurs. Any help would be much...
8
by: BillCo | last post by:
I'm updating a legacy app with table naming that makes baby jesus cry. It's a bit of a spider web though... no telling when and where the tables will be called by name. So I wrote this for renaming...
3
by: wess | last post by:
there are some primary key columns which are autoincrement in some tables. for exampl page_id, now we have deleted many pages in old server so there are many gaps between the page_id even the it...
4
by: Artie | last post by:
A few months ago a customer moved from SQL 2000 to SQL 2005. The db was backed up on SQL 2000 and restored to SQL 2005. The application using this data works on SQL 2005 but takes no advantage of...
2
by: Ilyas | last post by:
Hi all I need to implmenet paging across different tables. The tables all have a different name eg Data01, data02 data03 etc, however they are columns which are common to each table, but each...
5
by: kashif73 | last post by:
I have a text file with hundreds of records. each line contain 1250 values seperated by a semicolon. I have created 2 tables in SQL server 2000, one with 1000 columns & the second with 250 columns....
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.