2

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).

<snip for parameters getting passed in>

$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

2

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()
  • The sql worked for me(and was a better approach). Thanks for your help! – Chris L Nov 7 '14 at 17:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.