Imports Microsoft.SqlServer
Imports Microsoft.SqlServer.Management
Imports Microsoft.SqlServer.Management.Common
Imports Microsoft.SqlServer.Management.Smo
Imports Microsoft.SqlServer.Server
Imports system.Data

Module Util
    Public Sub ColumnDef_Copy(ByVal SourceTable As Smo.Table _
            , ByVal TargetTable As Smo.Table _
            , ByVal ColName As String)
        'copies a column definition from one table to another an then updates the
        ' target tables defintion in the database.
        '
        '2008-05-07 BYoung  Created.
        '
        'NOTE: untested.

        Dim SourceCol As Smo.Column = SourceTable.Columns(ColName)

        Dim TargetCol As New Smo.Column(TargetTable, ColName)
        'set the target column's properties using a Shallow Copy:
        With SourceCol
            TargetCol.DataType = .DataType
            TargetCol.Collation = .Collation
            If .Computed Then
                TargetCol.Computed = .Computed
                TargetCol.ComputedText = .ComputedText
                TargetCol.IsPersisted = .IsPersisted
            End If
            TargetCol.Default = .Default
            TargetCol.DefaultSchema = .DefaultSchema
            For Each xp As Smo.ExtendedProperty In .ExtendedProperties
                TargetCol.ExtendedProperties.Add( _
                    New Smo.ExtendedProperty(TargetCol, xp.Name, xp.Value))
            Next
            If .Identity Then
                TargetCol.Identity = .Identity
                TargetCol.IdentityIncrement = .IdentityIncrement
                TargetCol.IdentitySeed = .IdentitySeed
            End If
            TargetCol.NotForReplication = .NotForReplication
            TargetCol.Nullable = .Nullable
            TargetCol.RowGuidCol = .RowGuidCol
            TargetCol.Rule = .Rule
            TargetCol.RuleSchema = .RuleSchema
        End With

        'Find the column's position
        Dim ord As Integer = 0
        While SourceTable.Columns.Item(ord) IsNot SourceCol
            ord += 1
        End While

        TargetTable.Columns.Add(TargetCol, ord)
        TargetTable.Alter()
    End Sub
End Module