473,893 Members | 1,889 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

SQL 2000 renaming columns in tables

19 New Member
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.col00 1', 'id', 'COLUMN'
Mar 10 '07 #1
2 6807
bergy
89 New Member
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.col00 1', '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 New Member
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
8959
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 I_USR_LCK 4 T_CRT_RCD 5
3
16351
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 procedure that essentially inserts in the orders table the mail order, and then insert multiple orderdetails within the same transaction. I also need to do this via SQL 2000. Right now i have "x" amount of variables for all columns in my orders tables,...
1
2408
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 this denormalized table is for a web application). When we link to this table we are only seeing the first 255 columns in Access 2000. We have successfully linked several other Oracle tables getting all the columns but the number of columns on...
1
3457
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 identically structured, and the first row does NOT contain field names. Some (actually most) of the column names *are* the same in all of the spreadsheets.
6
4771
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 appreciated. Thanks in advance
8
2867
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 tables (I guess it would work for queries also). It changes the table name, changes the table name, queries that refer to it, form and report control sources, combo and list box row sources etc. Anyone inspired enough to figure out how to make...
3
1992
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 doest start with 1 because during the testing many initial pages were deleted. but when we move it to the new server with sql2005 it gives them numbers from 1,2,3 and so on. I need to keep existing 2000 keys. How can I acomplish this???
4
1887
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 new features. The db on SQL 2000 was about 2.9GB, now on SQL 2005, it is 16.5GB. The db is set to Simple recovery so trx log is only 2mb. The mdf file is 16.5GB, Management Studio shows only 5mb free space. There has not been a huge increase...
2
1353
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 table also has some unique columns My questions is that I want to display data from any one of these tables - I wont know which one until runtime, but since they contains large amounts of data, I only want to display say 10 at a time. Also
5
3167
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. my question is how can i import this textfile in to these 2 tables.and how i ensure that first 1000 values go to first table & the rest 250 in the next from each line of the text file. can be this be done via VB.NET or ASP? I would be grateful for...
0
11245
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
1
10929
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
9647
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
7175
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5859
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
6066
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4684
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4281
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3293
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

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.