Reordering Table Columns

I need a stored procedure that will reorder the columns for me, unfortunately, nothing else will do!

It's a relational database so data is accessed by tablename/fieldname.
If you have a system that is based on column order then you probably have big design problems which you should sort out.

As I said creating a view will present columns in a different order.

I understand what you've suggested vis-a-vis the views. It does not address the problem I am trying to solve. I need a stored procedure that will physically reorder the fields in a table.

In the example I gave, the number refer to the existing column by their physical order. The new order that is supplied should be used to change that physical order.

example: sp_ReorderTableColumns "tblPeople", "1,3,2"

tblPeople
-Name
-Address
-Phone

would now be

tblPeople
-Name
-Phone
-Address

You will probably need to create a new table with the columns in the desired order, recreate all the keys and indexes, then copy the data from old table to new table, drop old table and rename temp table to original name.

Seems like an awful lot of work for not much return.  What sort of problem could you be having where the order of the columns was significant?

Assumin you dont already have one, create a clustered index on the filed you want orederd and then drop the index. This will physicaly order the data.

Create procedure Reorder @Table varchar(50), @order varchar(50)
As

Declare @TableID as int, @Counter as smallint, @Query as varchar(200)
Select @TableID = id from sysobjects where name = @Table

if @TableID IS NOT NULL
  BEGIN
    if exists (select * from dbo.sysobjects where id = object_id(N'[tempTable]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
    drop table [tempTable]

    Set @Query = 'SELECT '
    Set @Counter=1
    IF RIGHT(@Order,1)!=',' Set @Order=@Order+','
    WHILE CHARINDEX(',',@order,@COUNTER+1) > 0
      BEGIN
            Select @Query = @Query
                  + Name + ', '
                  from syscolumns where id = @TableID and
                  ColOrder =cast( SUBSTRING(@ORDER,@COUNTER,CHARINDEX(',',@order,@COUNTER+1)-@COUNTER) as int)

            Select @Counter = CHARINDEX(',',@order,@COUNTER+1)+1
      END
    Set @Query = LEFT(@Query, LEN(@Query)-1 ) + ' INTO tempTable FROM ' + @Table

    Execute (@Query)
    exec ('drop table '+@Table)
    exec sp_rename 'tempTable', @Table
  END
go

Then you can do:

reorder 'Clients','2,3,4,5,6,7,8,9,10,11,12,13,14,1'
select * from Clients


It doesn't works if the table has foreign keys.
If there is any PK, it will dissapears after reordering...

basically this sp generates and executes this code for you:

 SELECT Field1, Field2, FieldN, ...  INTO tempTable FROM Clients
 drop table tempTable
 exec sp_rename 'tempTable', 'Clients'

I hope this help, (anyway, i'll never use this SP... your trying to do something really weird, and probably focusing the problem from a wrong point of view)

Saludos!
Gerardo Grignoli

I can't believe that noyone has ever written a stored procedure to do this. Gerardo_Grignoli's sp is a good start but is doesn't address constaints at all!

Create a clustered index on the field you want orderd and then drop the index. This will physicaly order the data.

htarlow: are you saying that doing what you prescribed will make the fields appear in the correct order when I do a select * or look at the table design in Enterprise Manager?

alaplume:

You don't say why you want or need to change the order. It's hard to find a comprehensive solution that meets your need if we don't know what those needs are. It may be that there is a better solution than changing the order, but until you tell us what the problem is, it's difficult to help, so please tell us.

The syscolumns table has a colorder column which *appears* to be the order in which the columns are shown. You could try copying the Northwind database and playing around with colorder, but take care, as it's marked 'for internal use only', so it could be disastrous.

>> I can't believe that noyone has ever written a stored procedure to do this. Gerardo_Grignoli's sp is a good start but is doesn't address constaints at all!

People don't write this sort of thing because people who know how to do it also know what a relational database is and so would realise it would not be a useful procedure.
Try addressing your underlying problem rather than trying to do something dangerous like this.