commands

^

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
-ColumnObject

If you want to get fancy, you can build your own column objects and pass them in

Alias
Required False
Pipeline false
Default Value
-AnsiNullsStatus

No information provided by Microsoft

Alias
Required False
Pipeline false
Default Value False
-ChangeTrackingEnabled

No information provided by Microsoft

Alias
Required False
Pipeline false
Default Value False
-DataSourceName

No information provided by Microsoft

Alias
Required False
Pipeline false
Default Value
-Durability

No information provided by Microsoft

Alias
Required False
Pipeline false
Default Value
-ExternalTableDistribution

No information provided by Microsoft

Alias
Required False
Pipeline false
Default Value
-FileFormatName

No information provided by Microsoft

Alias
Required False
Pipeline false
Default Value
-FileGroup

No information provided by Microsoft

Alias
Required False
Pipeline false
Default Value
-FileStreamFileGroup

No information provided by Microsoft

Alias
Required False
Pipeline false
Default Value
-FileStreamPartitionScheme

No information provided by Microsoft

Alias
Required False
Pipeline false
Default Value
-FileTableDirectoryName

No information provided by Microsoft

Alias
Required False
Pipeline false
Default Value
-FileTableNameColumnCollation

No information provided by Microsoft

Alias
Required False
Pipeline false
Default Value
-FileTableNamespaceEnabled

No information provided by Microsoft

Alias
Required False
Pipeline false
Default Value False
-HistoryTableName

No information provided by Microsoft

Alias
Required False
Pipeline false
Default Value
-HistoryTableSchema

No information provided by Microsoft

Alias
Required False
Pipeline false
Default Value
-IsExternal

No information provided by Microsoft

Alias
Required False
Pipeline false
Default Value False
-IsFileTable

No information provided by Microsoft

Alias
Required False
Pipeline false
Default Value False
-IsMemoryOptimized

No information provided by Microsoft

Alias
Required False
Pipeline false
Default Value False
-IsSystemVersioned

No information provided by Microsoft

Alias
Required False
Pipeline false
Default Value False
-Location

No information provided by Microsoft

Alias
Required False
Pipeline false
Default Value
-LockEscalation

No information provided by Microsoft

Alias
Required False
Pipeline false
Default Value
-Owner

No information provided by Microsoft

Alias
Required False
Pipeline false
Default Value
-PartitionScheme

No information provided by Microsoft

Alias
Required False
Pipeline false
Default Value
-QuotedIdentifierStatus

No information provided by Microsoft

Alias
Required False
Pipeline false
Default Value False
-RejectSampleValue

No information provided by Microsoft

Alias
Required False
Pipeline false
Default Value 0
-RejectType

No information provided by Microsoft

Alias
Required False
Pipeline false
Default Value
-RejectValue

No information provided by Microsoft

Alias
Required False
Pipeline false
Default Value 0
-RemoteDataArchiveDataMigrationState

No information provided by Microsoft

Alias
Required False
Pipeline false
Default Value
-RemoteDataArchiveEnabled

No information provided by Microsoft

Alias
Required False
Pipeline false
Default Value False
-RemoteDataArchiveFilterPredicate

No information provided by Microsoft

Alias
Required False
Pipeline false
Default Value
-RemoteObjectName

No information provided by Microsoft

Alias
Required False
Pipeline false
Default Value
-RemoteSchemaName

No information provided by Microsoft

Alias
Required False
Pipeline false
Default Value
-RemoteTableName

No information provided by Microsoft

Alias
Required False
Pipeline false
Default Value
-RemoteTableProvisioned

No information provided by Microsoft

Alias
Required False
Pipeline false
Default Value False
-ShardingColumnName

No information provided by Microsoft

Alias
Required False
Pipeline false
Default Value
-TextFileGroup

No information provided by Microsoft

Alias
Required False
Pipeline false
Default Value
-TrackColumnsUpdatedEnabled

No information provided by Microsoft

Alias
Required False
Pipeline false
Default Value False
-HistoryRetentionPeriod

No information provided by Microsoft

Alias
Required False
Pipeline false
Default Value 0
-HistoryRetentionPeriodUnit

No information provided by Microsoft

Alias
Required False
Pipeline false
Default Value
-DwTableDistribution

No information provided by Microsoft

Alias
Required False
Pipeline false
Default Value
-RejectedRowLocation

No information provided by Microsoft

Alias
Required False
Pipeline false
Default Value
-OnlineHeapOperation

No information provided by Microsoft

Alias
Required False
Pipeline false
Default Value False
-LowPriorityMaxDuration

No information provided by Microsoft

Alias
Required False
Pipeline false
Default Value 0
-DataConsistencyCheck

No information provided by Microsoft

Alias
Required False
Pipeline false
Default Value False
-LowPriorityAbortAfterWait

No information provided by Microsoft

Alias
Required False
Pipeline false
Default Value
-MaximumDegreeOfParallelism

No information provided by Microsoft

Alias
Required False
Pipeline false
Default Value 0
-IsNode

No information provided by Microsoft

Alias
Required False
Pipeline false
Default Value False
-IsEdge

No information provided by Microsoft

Alias
Required False
Pipeline false
Default Value False
-IsVarDecimalStorageFormatEnabled

No information provided by Microsoft

Alias
Required False
Pipeline false
Default Value False
-Passthru

Don't create the table, just print the table script on the screen.

Alias
Required False
Pipeline false
Default Value False
-InputObject

Allows piped input from Get-DbaDatabase

Alias
Required False
Pipeline true (ByValue)
Default Value
-EnableException

By default, when something goes wrong we try to catch it, interpret it and give you a friendly warning message.
This avoids overwhelming you with "sea of red" exceptions, but is inconvenient because it basically disables advanced scripting.
Using this switch turns this "nice by default" feature off and enables you to catch exceptions with your own try/catch.

Alias
Required False
Pipeline false
Default Value False
-WhatIf

Shows what would happen if the command were to run. No actions are actually performed.

Alias wi
Required False
Pipeline false
Default Value
-Confirm

Prompts you for confirmation before executing any changing operations within the command.

Alias cf
Required False
Pipeline false
Default Value