Skip to content

Instantly share code, notes, and snippets.

@SQLDBAWithABeard
Created September 29, 2019 09:08
Show Gist options
  • Save SQLDBAWithABeard/7cf4cd34afeb20155465378a507f68fe to your computer and use it in GitHub Desktop.
Save SQLDBAWithABeard/7cf4cd34afeb20155465378a507f68fe to your computer and use it in GitHub Desktop.
new tbale with constraint
$tablename = 'BeardTable'
$ColumnMap = @()
$int = New-Object Microsoft.SqlServer.Management.Smo.DataType("Int")
$varchar = New-Object Microsoft.SqlServer.Management.Smo.DataType("varchar")
$datetime = New-Object Microsoft.SqlServer.Management.Smo.DataType("datetime")
$identityColumn = New-Object Microsoft.SqlServer.Management.Smo.Column($table, "Id", $int)
$identityColumn.Nullable = $false
$identityColumn.Identity = $true
$identityColumn.IdentitySeed = 1
$ConstraintColumn = New-Object Microsoft.SqlServer.Management.Smo.Column($tablename, "constraint", $varchar)
$ConstraintColumn.Nullable = $false
$ConstraintColumn.AddDefaultConstraint("DefConstraint")
$ConstraintColumn.DefaultConstraint.Text = 'Beard'
$TimeColumn = New-Object Microsoft.SqlServer.Management.Smo.Column($tablename, "Update_Time", $varchar)
$TimeColumn.Nullable = $false
$TimeColumn.AddDefaultConstraint("DefTime")
$TimeColumn.DefaultConstraint.Text = 'CURRENT_TIMESTAMP'
$UserColumn = New-Object Microsoft.SqlServer.Management.Smo.Column($tablename, "Update_User", $varchar)
$UserColumn.Nullable = $false
$UserColumn.AddDefaultConstraint("DefUser")
$UserColumn.DefaultConstraint.Text = 'SUSER_NAME()'
$ColumnMap += $identityColumn
$ColumnMap += $ConstraintColumn
$ColumnMap += $TimeColumn
$ColumnMap += $UserColumn
New-DbaDbTable -Database pubs -Name $tablename -ColumnObject $ColumnMap
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment