Order of Columns - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Order of Columns

Author

Topic

dnf999
Constraint Violating Yak Guru

253 Posts

Posted - 2008-01-26 : 07:20:59

How do you change the order of columns in a table?

i.e.

ID
Forename
Last_name
Middle_name

and I want

ID
Forename
Middle_Name
Last_Name

I've tried to update the ordinal_position in Information_schema.columns, but this is not permissible. I do not want to create a new table with the re-ordered columns, so is there any way to do this without going through object explorer and modifying the table manually?

I want a programmable solution, if one exists!

thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-01-26 : 07:23:29

why the order of columns in a table is important to you ?

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-01-26 : 07:31:02

quote:
I want a programmable solution

SELECT
ID,
Forename,
Middle_Name,
Last_Name
FROM YOURTABLE

KH
[spoiler]Time is always against us[/spoiler]

dnf999
Constraint Violating Yak Guru

253 Posts

Posted - 2008-01-26 : 07:35:47

for when I combine mutiple tables with the same field but different field ordering.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-01-26 : 07:42:46

you can define the ordering you want in the SELECT statement

KH
[spoiler]Time is always against us[/spoiler]

dnf999
Constraint Violating Yak Guru

253 Posts

Posted - 2008-01-26 : 08:04:09

I am trying to avoid that.

i.e.

I want a query like

select * into test
from Table1
union all
select * from Table2

Now Table1 and Table2 will have the same columns, but different ordering and if each table contains 100 columns, I do not want to write all of them out.

I want to know how to write code so i can say move columnA to the end of a table.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-01-26 : 08:17:08

quote:
Originally posted by dnf999

I am trying to avoid that.

i.e.

I want a query like

select * into test
from Table1
union all
select * from Table2

Now Table1 and Table2 will have the same columns, but different ordering and if each table contains 100 columns, I do not want to write all of them out.

I want to know how to write code so i can say move columnA to the end of a table.

This is not a good practice. It is best that you specify the required column in your SELECT statement.

KH
[spoiler]Time is always against us[/spoiler]

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-01-26 : 08:19:40

>>I want a programmable solution
You shouldn't use "select *" in any persisted code anyway.

If you're too lazy to type out the column list

then why don't you simply use information_schema.columns to generate the column list from Table1 and use it for the column list of both tables?


select char(9) + column_name + ',' 
from   information_schema.columns 
where  table_name = 'Table1'
order by ordinal_position

Be One with the Optimizer
TG

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2008-01-28 : 05:28:40

The order of the columns is a display issue and should be handled at the SELECT statement --> client application end.
If you find you are selecting data from multiple tables and there are columns with identical names, use table aliases and column aliases

Jack Vamvas
--------------------
Search IT jobs from multiple sources- http://www.ITjobfeed.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources