Hi,
I am trying to alter a large table (with 100+ fields) so the fields are
ordered alphabetically. Is there a quicker and smarter way of doing this,
other then manual one-by-one?
Thanks,

Zoran

There is no supported method in SQL Server of moving columns in a table
except by dropping and re-creating the column. But you certainly don't have
to do it one-by-one...

Let's say we had the following table:

CREATE TABLE out_of_order
(
ColZ INT,
ColY INT,
ColX INT
)


We could create a new table with all of the same columns, in the right
order:

CREATE TABLE in_order
(
ColX INT,
ColY INT,
ColZ INT
)

.... and then INSERT all of the data from the other table:

INSERT in_order (ColX, ColY, ColZ)
SELECT ColX, ColY, ColZ
FROM out_of_order

.... and then it's a simple matter of dropping the old table and re-naming
the new one:

DROP TABLE out_of_order

sp_rename 'in_order', 'out_of_order', 'table'


--
Adam Machanic
Pro SQL Server 2005, available now
www.apress.com/book/bookDisplay.html?bID=457
--


"zknezic" <zknezic@discussions.microsoft.com> wrote in message
news:A07BEC3D-5021-4FD9-9C04-27623E472474@microsoft.com...
> Hi,
> I am trying to alter a large table (with 100+ fields) so the fields are
> ordered alphabetically. Is there a quicker and smarter way of doing this,
> other then manual one-by-one?
> Thanks,
>
> Zoran

Thanks Adam,
At some point I started contemplating the idea of updating syscolumns table
(changing colorder). Firstly, modifying this table was not allowed by
default. Secondly, it appears that this could be a messy job. I'll stick with
your suggestion, clean and simple...

Thank you,

zknezic

"Adam Machanic" wrote:

> There is no supported method in SQL Server of moving columns in a table
> except by dropping and re-creating the column. But you certainly don't have
> to do it one-by-one...
>
> Let's say we had the following table:
>
> CREATE TABLE out_of_order
> (
> ColZ INT,
> ColY INT,
> ColX INT
> )
>
>
> We could create a new table with all of the same columns, in the right
> order:
>
> CREATE TABLE in_order
> (
> ColX INT,
> ColY INT,
> ColZ INT
> )
>
> .... and then INSERT all of the data from the other table:
>
> INSERT in_order (ColX, ColY, ColZ)
> SELECT ColX, ColY, ColZ
> FROM out_of_order
>
> .... and then it's a simple matter of dropping the old table and re-naming
> the new one:
>
> DROP TABLE out_of_order
>
> sp_rename 'in_order', 'out_of_order', 'table'
>
>
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> www.apress.com/book/bookDisplay.html?bID=457
> --
>
>
> "zknezic" <zknezic@discussions.microsoft.com> wrote in message
> news:A07BEC3D-5021-4FD9-9C04-27623E472474@microsoft.com...
> > Hi,
> > I am trying to alter a large table (with 100+ fields) so the fields are
> > ordered alphabetically. Is there a quicker and smarter way of doing this,
> > other then manual one-by-one?
> > Thanks,
> >
> > Zoran
>
>
>

zknezic wrote:
> *Hi,
> I am trying to alter a large table (with 100+ fields) so the fields
> are
> ordered alphabetically. Is there a quicker and smarter way of doing
> this,
> other then manual one-by-one?
> Thanks,
>
> Zoran *

Not really. Either way, it's going to involve a lot of data movement
on the backend.

Is it possible for you to just put a view on the front-end and order
the columns there and just allow your end users to use the view?



--
arbert
------------------------------------------------------------------------
Posted via http://www.codecomments.com
------------------------------------------------------------------------

That is a possibility, it is just a matter of breaking a habit of those who
are using the table regularly. As they say "If there is a will, there is a
way"...

"arbert" wrote:

>
> zknezic wrote:
> > *Hi,
> > I am trying to alter a large table (with 100+ fields) so the fields
> > are
> > ordered alphabetically. Is there a quicker and smarter way of doing
> > this,
> > other then manual one-by-one?
> > Thanks,
> >
> > Zoran *
>
> Not really. Either way, it's going to involve a lot of data movement
> on the backend.
>
> Is it possible for you to just put a view on the front-end and order
> the columns there and just allow your end users to use the view?
>
>
>
> --
> arbert
> ------------------------------------------------------------------------
> Posted via http://www.codecomments.com
> ------------------------------------------------------------------------
>
>