Re: Rearrange fields alphabetically

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@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> news:A07BEC3D-5021-4FD9-9C04-27623E472474@xxxxxxxxxxxxxxxx
> > 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
>
>
>
.

Relevant Pages
Loading