New-DbaDbTable


Author	Chrissy LeMaire (@cl)
Availability	Windows, Linux, macOS

Want to see the source code for this command? Check out New-DbaDbTable on GitHub.
Want to see the Bill Of Health for this command? Check out New-DbaDbTable.

Synopsis

Creates a new table in a database

Description

Creates a new table in a database

Syntax

New-DbaDbTable
    [[-SqlInstance] <DbaInstanceParameter[]>]
    [[-SqlCredential] <PSCredential>]
    [[-Database] <String[]>]
    [[-Name] <String>]
    [[-Schema] <String>]
    [[-ColumnMap] <Hashtable[]>]
    [[-ColumnObject] <Column[]>]
    [-AnsiNullsStatus]
    [-ChangeTrackingEnabled]
    [[-DataSourceName] <String>]
    [[-Durability] {SchemaOnly | SchemaAndData}]
    [[-ExternalTableDistribution] {Sharded | Replicated | RoundRobin | None}]
    [[-FileFormatName] <String>]
    [[-FileGroup] <String>]
    [[-FileStreamFileGroup] <String>]
    [[-FileStreamPartitionScheme] <String>]
    [[-FileTableDirectoryName] <String>]
    [[-FileTableNameColumnCollation] <String>]
    [-FileTableNamespaceEnabled]
    [[-HistoryTableName] <String>]
    [[-HistoryTableSchema] <String>]
    [-IsExternal]
    [-IsFileTable]
    [-IsMemoryOptimized]
    [-IsSystemVersioned]
    [[-Location] <String>]
    [[-LockEscalation] {Table | Disable | Auto}]
    [[-Owner] <String>]
    [[-PartitionScheme] <String>]
    [-QuotedIdentifierStatus]
    [[-RejectSampleValue] <Double>]
    [[-RejectType] {Value | Percentage | None}]
    [[-RejectValue] <Double>]
    [[-RemoteDataArchiveDataMigrationState] {Disabled | PausedOutbound | PausedInbound | Outbound | Inbound | Paused}]
    [-RemoteDataArchiveEnabled]
    [[-RemoteDataArchiveFilterPredicate] <String>]
    [[-RemoteObjectName] <String>]
    [[-RemoteSchemaName] <String>]
    [[-RemoteTableName] <String>]
    [-RemoteTableProvisioned]
    [[-ShardingColumnName] <String>]
    [[-TextFileGroup] <String>]
    [-TrackColumnsUpdatedEnabled]
    [[-HistoryRetentionPeriod] <Int32>]
    [[-HistoryRetentionPeriodUnit] {Day | Week | Month | Year | Undefined | Infinite}]
    [[-DwTableDistribution] {Undefined | None | Hash | Replicate | RoundRobin}]
    [[-RejectedRowLocation] <String>]
    [-OnlineHeapOperation]
    [[-LowPriorityMaxDuration] <Int32>]
    [-DataConsistencyCheck]
    [[-LowPriorityAbortAfterWait] {None | Blockers | Self}]
    [[-MaximumDegreeOfParallelism] <Int32>]
    [-IsNode]
    [-IsEdge]
    [-IsVarDecimalStorageFormatEnabled]
    [-Passthru]
    [[-InputObject] <Database[]>]
    [-EnableException]
    [-WhatIf]
    [-Confirm]
    [<CommonParameters>]

Examples

Example: 1

PS C:\> $col = @{
>> Name      = 'test'
>> Type      = 'varchar'
>> MaxLength = 20
>> Nullable  = $true
>> }
PS C:\> New-DbaDbTable -SqlInstance sql2017 -Database tempdb -Name testtable -ColumnMap $col

Creates a new table on sql2017 in tempdb with the name testtable and one column

Example: 2

PS C:\> $cols = @( )
>> $cols += @{
>>     Name              = 'Id'
>>     Type              = 'varchar'
>>     MaxLength         = 36
>>     DefaultExpression = 'NEWID()'
>> }
>> $cols += @{
>>     Name          = 'Since'
>>     Type          = 'datetime2'
>>     DefaultString = '2021-12-31'
>> }
PS C:\> New-DbaDbTable -SqlInstance sql2017 -Database tempdb -Name testtable -ColumnMap $cols

Creates a new table on sql2017 in tempdb with the name testtable and two columns.
Uses "DefaultExpression" to interpret the value "NEWID()" as an expression regardless of the data type of the column.
Uses "DefaultString" to interpret the value "2021-12-31" as a string regardless of the data type of the column.

Example: 3

PS C:\> # Create collection
>> $cols = @()
>> # Add columns to collection
>> $cols += @{
>>     Name      = 'testId'
>>     Type      = 'int'
>>     Identity  = $true
>> }
>> $cols += @{
>>     Name      = 'test'
>>     Type      = 'varchar'
>>     MaxLength = 20
>>     Nullable  = $true
>> }
>> $cols += @{
>>     Name      = 'test2'
>>     Type      = 'int'
>>     Nullable  = $false
>> }
>> $cols += @{
>>     Name      = 'test3'
>>     Type      = 'decimal'
>>     MaxLength = 9
>>     Nullable  = $true
>> }
>> $cols += @{
>>     Name      = 'test4'
>>     Type      = 'decimal'
>>     Precision = 8
>>     Scale = 2
>>     Nullable  = $false
>> }
>> $cols += @{
>>     Name      = 'test5'
>>     Type      = 'Nvarchar'
>>     MaxLength = 50
>>     Nullable  =  $false
>>     Default  =  'Hello'
>>     DefaultName = 'DF_Name_test5'
>> }
>> $cols += @{
>>     Name      = 'test6'
>>     Type      = 'int'
>>     Nullable  =  $false
>>     Default  =  '0'
>> }
>> $cols += @{
>>     Name      = 'test7'
>>     Type      = 'smallint'
>>     Nullable  =  $false
>>     Default  =  100
>> }
>> $cols += @{
>>     Name      = 'test8'
>>     Type      = 'Nchar'
>>     MaxLength = 3
>>     Nullable  =  $false
>>     Default  =  'ABC'
>> }
>> $cols += @{
>>     Name      = 'test9'
>>     Type      = 'char'
>>     MaxLength = 4
>>     Nullable  =  $false
>>     Default  =  'XPTO'
>> }
>> $cols += @{
>>     Name      = 'test10'
>>     Type      = 'datetime'
>>     Nullable  =  $false
>>     Default  =  'GETDATE()'
>> }
PS C:\> New-DbaDbTable -SqlInstance sql2017 -Database tempdb -Name testtable -ColumnMap $cols

Creates a new table on sql2017 in tempdb with the name testtable and ten columns.

Optional Parameters

-SqlInstance

The target SQL Server instance or instances.


Alias
Required	False
Pipeline	false
Default Value

-SqlCredential

Login to the target instance using alternative credentials. Accepts PowerShell credentials (Get-Credential).
Windows Authentication, SQL Server Authentication, Active Directory - Password, and Active Directory - Integrated are all supported.
For MFA support, please use Connect-DbaInstance.


Alias
Required	False
Pipeline	false
Default Value

-Database

The database or databases where the table will be created


Alias
Required	False
Pipeline	false
Default Value

-Name

The name of the table


Alias
Required	False
Pipeline	false
Default Value

-Schema

The schema for the table, defaults to dbo


Alias
Required	False
Pipeline	false
Default Value	dbo

-ColumnMap

Hashtable for easy column creation. See Examples for details


Alias
Required	False
Pipeline	false
Default Value