Change the ordinal for existing column 1

[fafrazh99]

Is there any way to change the ordinal for existing column using TSQL instead of Enterprise Manager?

Is it possible to use one of the system stored procedures to get it done?

Thanks in advance,

Frank

 

[NoCoolHandle]

Frank, my first question is...
why does it matter.. You can change the order you display your columns just by changing your column order in the select clause of a SQL Statement..

Changing a column order is not simple..
What EM does behind the scenes is (and you would have to duplicate it in code)

create a new table with the new col order
import the old data into the new table
drop the old table
rename the new table

of course constraints can cause problems. You will need to script all foreign keys, check constraints, default constraints.. drop them and then recreate them in the new table...

Bottom line, yes you can do it, no, it is not simple, EM isn't that bad - it makes a tough job easy...BUT WHY WORRY ABOUT COL ORDER, your select clause can display data in any order and storage wise it doesn't matter eiter.. The only time it makes a difference is with select * and that is just bad practice.

Rob

 

[plantj]

NoCoolHandle, I must say in fafrazh99's defense that (i) the careful ordering of columns can pay off by aiding the readability of the schema (ii) select * is bad practise in most situations but it can be used often by support and maintenance personnel (iii) fafrazh99 may have many tables to re-jig, making EM a poor choice.

fafrazh99, get your act together and concentrate on good SELECT design

 

[fafrazh99]

Thanks Plantj!

Being DBA for years, I found the interests of programmer and DBA are not always in the same direction. Plantj, you are the few exceptions.

Due to the constant change of table structure, the ordinal of field has to be changed frequently in my shop. EM is not that convenient any more. Let the search continue ...

Thanks,

Frank

 

[NoCoolHandle]

Ok, assuming that you really want to do this..
You can build a script/applictaion that echos what EM does..
The sytem Tables you need to look at are sysobjects, syscolumns, sysconstraints, syscomments, sysforeignkeys, sysprotects, sysindexes,sysindexkeys, syspermissions and then maybe systypes.. Quite a twisted path.

Remember during the process of just changing the ordinal position of a column you will be dissableing constraint checking for current modifications and querys might miss joined rows until you finish... (not that data integrity should mean anyting to you)

Blows me away that you would give a star to someone that just agrees with bad practices that your organization uses..

Select * type practices can lead to extreamly bad use of network resources (not just db). As a NetWork Person I would expect that Network Bandwith is an issue with you. But then Giganet networks allow for bad practices to go unnoticed.

Select * allows you to now bring Blob data accross when all you want is the employeeid,firstname,and lastname. Boom what used to be 1k a row just bloomed to 1mb per row.

Rob
MCSE, MCSD, MCDBA
PS I personally manage 156 SQL Servers, 4 web servers, 15 web apps + 4 24/7/365 desktop apps that run over 1,000,000 transactions per day. Doing things just because it would be nice isn't always the best option - a few years in teh school of hard knocks tells me to Design carefully, look to the future and when you change stuff.. Change the code you use to look at the data/metadata to make it all make sence to your business logic and business practices..

 

[fafrazh99]

"Select *" is a bad practice. There is no question about it. I'm fighting for it with programmers all the time. Thanks for the detail explanation about it, Rob!

I never thought of my almost yes or no type of question will turn up the heat like this.

Rob, Thanks for your advices any way.

Frank