Why am I missing some output?

Archiving to avoid ID Exhaustion

We are about to run out of IDs in one of the tables of our OLTP logging system. It's my job to find a way to 'archive' the existing row data in the full table and in all referring tables so that we can continue to log new data into the tables.

The quickest way for us to archive data in one table is simply to rename the target table and all dependent objects -- junction tables, indexes, constraints. It's fast and keeps the data intact. To finish the job, we have to create new, empty copies of all the objects with their old names. If we do all these operations in one transaction, the stored procedures that insert new data will not fail because of missing tables.

An attempt to solve using PowerShell

I've putting together a PowerShell script to generate a T-SQL script to perform the archive operation.

The script is not generating as many rename statements as it should, and I don't understand why.

The script loads SMO, sets the names of target objects, and sets up a capture-only connection to the server - this is so I can capture rename commands for inspection later:

Add-Type -AssemblyName "Microsoft.SqlServer.Smo, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91";

$ServerName = 'CLOUDCORP\LOGGING'
$DatabaseName = 'Logging'
$TableName = 'tbDataRequests'
$TableSchemaName = 'Logging'

$Server = New-Object Microsoft.SqlServer.Management.Smo.Server $ServerName
$Server.ConnectionContext.SqlExecutionModes = [Microsoft.SqlServer.Management.Common.SqlExecutionModes]::CaptureSql

Next it creates an array of objects that are going to be archived by renaming. The target table itself, all referencing tables, and all keys and all indexes of these tables are to be archived:

```
$Database = $Server.Databases[$DatabaseName]

$Table = @($Database.Tables[$TableName, $TableSchemaName])

$ReferencingTables = $Database.Tables.ForeignKeys |
? { $_.ReferencedTable -eq $TableName -and $_.ReferencedTableSchema -eq $TableSche

You cannot loop through objects in the way you have coded. Here's the working code:

Add-Type -AssemblyName "Microsoft.SqlServer.Smo, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91";

$ServerName = 'xyzabc123'
$DatabaseName = 'test1'
$TableName = 'main'
$TableSchemaName = 'dbo'

$Server = New-Object Microsoft.SqlServer.Management.Smo.Server $ServerName
$Server.ConnectionContext.SqlExecutionModes = [Microsoft.SqlServer.Management.Common.SqlExecutionModes]::CaptureSql

$Database = $Server.Databases[$DatabaseName]

$Table = $Database.Tables[$TableName, $TableSchemaName]

$TablesToArchive = @($Table)
$Database.Tables | Select -ExpandProperty ForeignKeys | % {
    if($_.ReferencedTable -eq $TableName -and $_.ReferencedTableSchema -eq $TableSchemaName)
        { $TablesToArchive += $_.Parent }
}
# $TablesToArchive | % { $_.Name }

$ObjectsToArchive = ($TablesToArchive | Select -ExpandProperty Indexes) + ($TablesToArchive | Select -ExpandProperty ForeignKeys)
# $ObjectsToArchive | % { $_.Name }

$ObjectsToArchive | % {
    $_.Rename($_.Name + '_archive')
}
$RenameCommands = $Server.ConnectionContext.CapturedSql.Text
# $RenameCommands

For example, you were expecting

$Database.Tables.ForeignKeys

To give you a combined ForeignKeys collection made up of ForeignKeys of each Tables object. If you added the debug commands

# $ObjectsToArchive | % { $_.Name }

You would have been able to track it down very quickly.

StackExchange Database Administrators Q#30772, answer score: 6