MS SQL Data Warehousing - Rearrange fields alphabetically

Author

Rearrange fields alphabetically

zknezic

2005-11-01, 11:23 am

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

arbert

2005-11-05, 12:05 pm

quote:
Originally
posted by zknezic
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?

Adam Machanic

2005-11-08, 9:23 am

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@microso
ft.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

2005-11-08, 9:23 am

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@microso
ft.com...
>
>
>

zknezic

2005-11-08, 3:56 pm

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
> ------------------------------------------------------------------------
>
>

Sponsored Links