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.
| Author |
Topic |
|
wes
Starting Member
1 Post |
 Posted - 2004-07-19 : 02:56:05
|
| Can I change the order of columns in a table?Not use the "select" language.I want to display data by the t-sql,"Select * from tableA", and the tableA have four columns: " A,B,C,D " in order. I know I can write the t-sql as " Select C,B,D,A from tableA" to control the order to display. But I want to write an application to add the new columns to the tableA or change the order dynamicly. I don't know how many columns will I add or how will I order it, so that I cannot write the t-sql first.Is there any way to solve this? |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-07-19 : 03:32:57
|
| Well you could try updating syscolumns but I would stronlgly advise against it. The data won't be physically stored in that order anyway if that's what you are thinking.The idea is that it shouldn't matter what order the columns are in. If you think you need something like this then don't access the table but interface using a view which selects the columns in the order you want.But I would review the design of whatever you are doing because you are probably heading for problems.Altering tables also causes oddities in the database and should be avoided if possible:seehttp://www.nigelrivett.net/AlterTableProblems.html==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2004-07-19 : 04:36:46
|
| Wes, I'd be interested to know why you want to do this 'coz then we may have a smart idea for you :-)But that apart the general advice is "don't use SELECT *" - in addition to the points Nigel makes, we had a client who did this, their application ran fine, and then one Bright Sunny Morning someone added 4 massive TEXT columns to all the main tables and overnight the SELECT * stuff made everything run like a dog (and of course 99% of the application didn't need the new columns).That said, I sometimes use INSERT INTO (column name list) SELECT *where I want it to fail if ever a new column is added ...Kristen |
|
|
|
|
|
|
|
|
How to change order of columns in a table...