473,231 Members | 2,006 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,231 software developers and data experts.

Changing column order in a table

This subject has been posted several times, but I haven't seen a good
answer.

Problem:
I want to change the order of the columns in a table using T-SQL only.

Explanation:
After running your code, I want to see the following table...

CREATE TABLE [dbo].[TableName] (
[First_Column] [int] NULL ,
[Second_Column] [varchar] (20) NULL
) ON [PRIMARY]

look like this...

CREATE TABLE [dbo].[TableName] (
[Second_Column] [varchar] (20) NULL ,
[First_Column] [int] NULL
) ON [PRIMARY]

Limitations:
Don't post if your post would fall in the following categories:
1. If you don't think it can be done
2. If you think Enterprise Manager is the only way to do this
3. If you think I should just change the order of my Select
statements
4. If you want to state that order column doesn't matter in a
relational database
5. If you want to ask me why I want to do this

Wish:
Hopefully the answer WON'T involve creating a brand new table, moving
the data from old to new, dropping the old table, then renaming the
new table to the old name. Yes, I can do that. The table I'm working
with is extremely huge -- I don't want to do the data juggling.

Thanks in advance!
Jul 20 '05 #1
2 14263
Dan,

I hope this doesn't fall into your 5 commandments ;-) If by any reason the
order of columns is important to you, you can use a view. I do understand
that I'm not supposed to ask you why you want to do this, but why!!? :-) If
you tell us your original problem then you might be lucky enough to find
somebody that can give you some other alternatives. I'm sure you have a
reason for this. Without revealing much information about what you want to
do it's really hard to find the solution.

Good luck,
Shervin
"Dan Newton" <dn*****@scriptsave.com> wrote in message
news:28*************************@posting.google.co m...
This subject has been posted several times, but I haven't seen a good
answer.

Problem:
I want to change the order of the columns in a table using T-SQL only.

Explanation:
After running your code, I want to see the following table...

CREATE TABLE [dbo].[TableName] (
[First_Column] [int] NULL ,
[Second_Column] [varchar] (20) NULL
) ON [PRIMARY]

look like this...

CREATE TABLE [dbo].[TableName] (
[Second_Column] [varchar] (20) NULL ,
[First_Column] [int] NULL
) ON [PRIMARY]

Limitations:
Don't post if your post would fall in the following categories:
1. If you don't think it can be done
2. If you think Enterprise Manager is the only way to do this
3. If you think I should just change the order of my Select
statements
4. If you want to state that order column doesn't matter in a
relational database
5. If you want to ask me why I want to do this

Wish:
Hopefully the answer WON'T involve creating a brand new table, moving
the data from old to new, dropping the old table, then renaming the
new table to the old name. Yes, I can do that. The table I'm working
with is extremely huge -- I don't want to do the data juggling.

Thanks in advance!

Jul 20 '05 #2
dn*****@scriptsave.com (Dan Newton) wrote in message news:<28*************************@posting.google.c om>...
This subject has been posted several times, but I haven't seen a good
answer.

Problem:
I want to change the order of the columns in a table using T-SQL only.

Explanation:
After running your code, I want to see the following table...

CREATE TABLE [dbo].[TableName] (
[First_Column] [int] NULL ,
[Second_Column] [varchar] (20) NULL
) ON [PRIMARY]

look like this...

CREATE TABLE [dbo].[TableName] (
[Second_Column] [varchar] (20) NULL ,
[First_Column] [int] NULL
) ON [PRIMARY]

Limitations:
Don't post if your post would fall in the following categories:
1. If you don't think it can be done
2. If you think Enterprise Manager is the only way to do this
3. If you think I should just change the order of my Select
statements
4. If you want to state that order column doesn't matter in a
relational database
5. If you want to ask me why I want to do this

Wish:
Hopefully the answer WON'T involve creating a brand new table, moving
the data from old to new, dropping the old table, then renaming the
new table to the old name. Yes, I can do that. The table I'm working
with is extremely huge -- I don't want to do the data juggling.

Thanks in advance!


You will have to create a new table, copy the data, then rename it.
This may be tedious, but it will work and it is supported. The only
'short cut' would be to attempt to hack syscolumns, but since many
columns are 'internal use only' and direct updates to system tables
are not supported, you have no guarantee of success, and no support
from Microsoft if you have problems (which may not appear at first).

Or as Shervin suggested, if you can provide more information on what
your issue is, there may be an alternative solution.

Simon
Jul 20 '05 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: Ike | last post by:
Suppose I have a table, and it is populated with many rows already. However, I realise I structured something wrong in my table. I dont want to have to go back and repopulate the entire table. ...
9
by: Jon Brunson | last post by:
Is it possible to use a DataAdapter to fill a DataTable, change the DataColumns of that DataTable (and maybe even it's name) and then commit those changes to the database (in my case SQL Server...
4
by: Johan Vervloet | last post by:
Does anybody know how I can change the default value for a column? I was trying to remove the default value in order to add the new one afterwards. This is what I tried: alter table...
2
by: Paolo Pignatelli | last post by:
I am trying to get an output/file like this (below) in an XML file (MyXmlFile.xml) (which I will use for a slide show) -- <gallery timer="3" order="sequential" fadetime="2" looping="yes"...
16
by: StenKoll | last post by:
Help needed in order to create a register of stocks in a company. In accordance with local laws I need to give each individual share a number. I have accomplished this by establishing three tables...
2
by: ColinWard | last post by:
Hi. I have a form which has as its recordsource an SQL string. The SQL String is as follows: SELECT * from CONTACTS where false. this ensures that there is no data loaded in the form when the...
7
by: Arnold | last post by:
Greetings Gurus, In a mainform's header, I have a combobox named comboStudents. The rowsource for this combobox is: SELECT -999 As StudentID, "<Add New Student>" As FullName, "aaa" As...
11
by: surya | last post by:
hello sir, i have a table emp ,it has three fields one is empno int ,second is ename varchar(20). and last is salary , emp empno ename salary ----------- ------------...
3
by: Beowulf | last post by:
I was just messing around with some ad hoc views and table returning UDFs today so I could look at and print out data from a small table and noticed something strange. If I stick my select...
0
by: VivesProcSPL | last post by:
Obviously, one of the original purposes of SQL is to make data query processing easy. The language uses many English-like terms and syntax in an effort to make it easy to learn, particularly for...
3
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 3 Jan 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). For other local times, please check World Time Buddy In...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
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
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...

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.