Rearrange fields alphabetically

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,
ZoranThere 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 wit
h
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 ha
ve
> 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...
>
>|||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:
> 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
> ---
>