Allen White

Copying a Table Structure into a New Table using SMO

This morning I was answering a question in the MSDN Forums about using SMO to copy the structure of an existing table to create a new one. Of course, this is very easy in Transact-SQL, as evidenced in this example:

	select * into NewTable
	from ExistingTable
	where 1=2

But there are times when you're building a management application using SMO and you want to work strictly with the objects. In this case, you need to iterate through the columns in the existing table, using their property values to create new corresponding columns in the new table. This code from a VB console application shows how it can be done:

        Dim srv As Server
        Dim srvConn As ServerConnection
        srv = New Server("MyServer")
        srvConn = srv.ConnectionContext
        srvConn.LoginSecure = True

        Dim db As Database
        db = srv.Databases("AdventureWorks")

        Dim tblExisting As Table
        Dim tblNew As Table

        tblExisting = db.Tables.Item("Employee", "HumanResources")
        tblNew = New Table(db, "NewEmp", "HumanResources")
        Dim colExistColumn As ColumnCollection
        Dim clmExist As Column
        colExistColumn = tblExisting.Columns
        For Each clmExist In colExistColumn
            Dim clmNew As Column
            clmNew = New Column(tblNew, clmExist.Name)
            clmNew.DataType = clmExist.DataType
            clmNew.Nullable = clmExist.Nullable
            tblNew.Columns.Add(clmNew)
        Next
        tblNew.Create()

Allen