You can do this easily through Enterprise Manager in the table design form. If you must do this using TSQL, then change your table through Enterprise Manager and instead of saving your changes use the button in the menu bar to script the changes. Copy the code and paste it into your application with whatever modifications you require.
If it's not practically useful, then it's practically useless.
Adding column to a table before an existing column
Actually, this can be very useful. Lets say your loading data using bulk insert. Your source data for the given table had columns in a particular order and your target has a different order. You have no control over the datafile creation process.