Generating foreign key drop powershell script with SMO

Question

I have the following powershell script to generate a script to drop foreign keys, using SMO ($scripter is a Scripter object, and $db is a Database object):

$scripter.Options.ScriptDrops = $true;
$scripter.Options.DriForeignKeys = $true;
$scripter.Options.DriChecks = $true;

$dbObjCollection = @();
foreach($tb in $db.Tables)
{
  $dbObjCollection += $tb.Checks;
  $dbObjCollection += $tb.ForeignKeys;
}

foreach ($dbObj in $dbObjCollection) 
{   
  $smoObjects = @();
  $smoObjects += $dbObj.Urn; 
  if ($dbObj.IsSystemObject -eq $false) 
  { 
     $sc = $scripter.EnumScript($smoObjects); 
  } 
}

But it doesn’t work; no data is written to the file specified in the FileName property of the Scripter object.

Can anyone provide guidance on how to set up the Scripter object to generate drops for foreign keys only, and what objects to pass to the Scripter object to make this happen?

Answer 1

This script works for me (note the call to $scripter.Script($dbObj) in the foreach loop).

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null

$s = New-Object Microsoft.SqlServer.Management.Smo.Server($serverInstance)
$db = $s.databases[$database]

$scripter = New-Object ('Microsoft.SqlServer.Management.Smo.Scripter') ($s)

$scripter.Options.ScriptDrops = $true;
$scripter.Options.DriForeignKeys = $true;
$scripter.Options.DriChecks = $true;

$dbObjCollection = @();
foreach($tb in $db.Tables)
{
  $dbObjCollection += $tb.Checks;
  $dbObjCollection += $tb.ForeignKeys;
}

foreach ($dbObj in $dbObjCollection) 
{   
  $smoObjects = @();
  $smoObjects += $dbObj.Urn; 
  if ($dbObj.IsSystemObject -eq $false) 
  { 
     $sc = $scripter.EnumScript($smoObjects); 
  } 

  $scripter.Script($dbObj)
}