Script Table Definition in Powershell I'm writing a powershell to create a duplicate table(in another schema) for the purposes of partition switching*.  I'm having problems adding the columns and the clustered column store index.

Here's what I have so far($schemaName is the destination schema).

&lt;snip for parameters getting passed in&gt;

$database = $server.Databases[$dbName]

$table = new-object Microsoft.SqlServer.Management.Smo.Table ($database, $tblName)

$table.Schema = $schemaName

# Next two lines are not working
foreach ($col in $tblname.columns) { $table.columns.Add($col) }
# $table.Columns.CopyTo($tblName.Columns, 0)

## NEED TO ADD CLUSTERED COLUMN STORE INDEX TO TABLE

Write-Output $table.Columns.Count # Getting zero column count
# This isn't working
# $table.Create()


Any ideas on how to add the columns/clustered column store index?  Or a better approach?


= PartitionManager, over on codeplex, doesn't support SQL Server 2014(clustered column store indexes), so I have to roll my own code.


edit title
Normally I would do somehthing like this using T-SQL, even if Powershell was running that T-SQL, eg

SELECT *
INTO dbo.copyTable
FROM dbo.originalTable
WHERE 1 = 2
GO

CREATE CLUSTERED COLUMNSTORE INDEX _CS ON dbo.copyTable
GO


However I did get the following Powershell to work as a simple example:

$instance = ".\sql2014"
$server = New-Object "Microsoft.SqlServer.Management.Smo.Server" $instance
$database = $server.Databases[$dbName]

$tblName = "cs_test"
$originalTable = $database.Tables[$tblName]

$newTable = New-Object ("Microsoft.SqlServer.Management.Smo.Table") ($database, "copyTable", "dbo")


# Loop through columns adding each one to new table
foreach ($col in $originalTable.Columns)
{
    $newCol = New-Object ("Microsoft.SqlServer.Management.Smo.Column") ($newTable, $col.Name, $col.DataType)
    $newCol.Nullable = $col.Nullable
    $newTable.Columns.Add($newCol)
}


# Add columnstore index
$idx = New-Object ("Microsoft.SqlServer.Management.Smo.Index")($newTable, "idx") ##,IndexType.ClusteredColumnStoreIndex)
$idx.IndexType = "ClusteredColumnStoreIndex"
$newTable.Indexes.Add($idx)

# Create the table
$newTable.Create()