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!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" <dnewton@.scriptsave.com> wrote in message
news:280c01a4.0312091312.2f52e02@.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!|||dnewton@.scriptsave.com (Dan Newton) wrote in message news:<280c01a4.0312091312.2f52e02@.posting.google.com>...
> 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