Create a new DataTable object based on an existing Microsoft SQL database table

New-DataTableFromSqlTable.ps1

function New-DataTableFromSqlTable{
	<#
		.SYNOPSIS
			Create a new (empty) DataTable object based on a SQL Table.

		.DESCRIPTION
			Create a new (empty) DataTable object based on a SQL Table.

		.PARAMETER DatabaseServer
			The Hostname of the database server.

		.PARAMETER DatabaseName
			The name of the database

		.PARAMETER TableName
			The name of the table you wish to create as an empty DataTable object.

		.EXAMPLE
			$dataTable = New-DataTableFromSqlTable -DatabaseServer DBServer01 -DatabaseName DB01 -TableName Table01

			Description
			-----------
			Will connect to DBServer01/DB01 and create a new DataTable object based on Table01 and save to $dataTable.

		.NOTES
			Name: New-DataTableFromSqlTable
			Author: Øyvind Kallstad
			Date: 18.02.2014
			Version: 1.0
	#>
	[CmdletBinding()]
	param(
		[Parameter(Mandatory = $true)]
		[Alias('Server')]
		[string]$DatabaseServer,

		[Parameter(Mandatory = $true)]
		[string]$DatabaseName,

		[Parameter(Mandatory = $true)]
		[string]$TableName
	)

	try{
		# connect to database server
		[Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | Out-Null
		$dbSrv = New-Object Microsoft.SqlServer.Management.SMO.Server $DatabaseServer

		# get the database
		$db = New-Object Microsoft.SqlServer.Management.SMO.Database
		$db = $dbSrv.Databases[$DatabaseName]

		# get the table
		$table = New-Object Microsoft.SqlServer.Management.SMO.Table
		$table = $db.Tables[$TableName]

		# get table columns
		$columns = New-Object Microsoft.SqlServer.Management.SMO.Column
		$columns = $table.Columns

		# create a datatable - this will be our returned object
		$dt = New-Object System.Data.DataTable($TableName)

		# an array to hold primary keys
		$keys = @()

		# iterate through each column
		foreach ($column in $columns){

			# convert SQL datatypes to .NET datatypes
			switch ($column.DataType.Name){
				'tinyint'		{$dataType = 'Byte'}
				'binary'		{$dataType = 'Byte[]'}
				'varbinary'		{$dataType = 'Byte[]'}
				'image'			{$dataType = 'Byte[]'}
				'timestamp'		{$dataType = 'Byte[]'}
				'bit'			{$dataType = 'Boolean'}
				'smallint'		{$dataType = 'Int16'}
				'int'			{$dataType = 'Int32'}
				'bigint'		{$dataType = 'Int64'}
				'real'			{$dataType = 'Single'}
				'float'			{$dataType = 'Double'}
				'decimal'		{$dataType = 'Decimal'}
				'numeric'		{$dataType = 'Decimal'}
				'money'			{$dataType = 'Decimal'}
				'smallmoney'		{$dataType = 'Decimal'}
				'time'			{$dataType = 'TimeSpan'}
				'date'			{$dataType = 'DateTime'}
				'datetime'		{$dataType = 'DateTime'}
				'datetime2'		{$dataType = 'DateTime'}
				'smalldatetime'		{$dataType = 'DateTime'}
				'xml'			{$dataType = 'Xml'}
				'nchar'			{$dataType = 'String'}
				'char'			{$dataType = 'String'}
				'nvarchar'		{$dataType = 'String'}
				'varchar'		{$dataType = 'String'}
				'ntext'			{$dataType = 'String'}
				'text'			{$dataType = 'String'}
				DEFAULT			{$dataType = 'string'}
			}

			# create a datacolumn object
			$dtColumn = New-Object System.Data.DataColumn ($column.Name), ($dataType)
			$dtColumn.AllowDBNull = $column.Nullable
			if ($column.Identity){
				$dtColumn.AutoIncrement = $true
				$dtColumn.AutoIncrementSeed = $column.IdentitySeed
				$dtColumn.AutoIncrementStep = $column.IdentityIncrement
			}
			# collect keys
			if ($column.InPrimaryKey){
				$keys += $dtColumn
			}

			# add the column to the datatable
			$dt.Columns.Add($dtColumn)
		}

		# add primary keys
		$dt.PrimaryKey = $keys
	}

	# catching exceptions
	catch{
		if ($_.Exception.InnerException){
			Write-Warning $_.Exception.InnerException.Message
		}
		else{
			Write-Warning $_.Exception.Message
		}
		exit
	}

	# the finished datatable object
	Write-Output @(,($dt))
}