Reorder Columns Using SMO

[Thomas H. Lanier]

Using SQL Server 2005 Express, VB.Net, and DotNet 2.0, how can I reorder the
columns in a table?
Below is code to rename a column. The works fine. How can I reorder the
table.columns collection after I rename a column? I like to keep the columns
in alphabetical order.

Something like below doesn't work:

col1 = table.Columns(3)
col2 = table.Columns(4)
table.Columns(3) = col2 'error, property Item is read only
table.Columns(4) = col1 'error, property item is read only

This doesn't work either:

col1 = table.Columns("Field1")
table.Columns.Remove("Field1") 'You cannot perform operation Remove on an
object in state Existing.
table.Columns.Add(col1,"Field2")

How can I save a copy of the column before I remove it so I can add it back?

Anyone know how to do this?

Thanks

Tommy Lanier
***@ces-web.com

__________________________________________________________________

Imports System.Data.SqlClient
Imports Microsoft.SqlServer.Management.Common
Imports Microsoft.SqlServer.Management.Smo
Imports Microsoft.SqlServer.Server

Public Class Form1

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button1.Click

Dim server As Server
Dim sc As ServerConnection
Dim cn As SqlConnection
Dim cs As String
Dim db As Database
Dim table As Table
Dim col As Column
cs = My.Settings.ConnectionString
cn = New SqlConnection(cs)
sc = New ServerConnection(cn)
server = New Server(sc)
db = server.Databases("DatabaseName")
table = db.Tables("TableName")
col = table.Columns("Field1")
col.Rename("NewField1")
col.Alter()
table.Alter()
End Sub

End Class

[Aaron Bertrand [SQL Server MVP]]

Post by Thomas H. Lanier
Using SQL Server 2005 Express, VB.Net, and DotNet 2.0, how can I reorder
the columns in a table?

You drop the table and re-create it. (This is what the graphical tools do
"for you" behind your back when you do this with point and click. Beware of
this fact when picking large tables for this exercise.)

Post by Thomas H. Lanier
I like to keep the columns in alphabetical order.

What is the logic behind this?

A

[Thomas H. Lanier]

Aaron,

Thanks for the reply.

I never realized such a drastic measure was necessary. I guess I should
reconsider whether it's really worth having to recreate the table just to
reorder the columns.

Tommy