Need to generate 1 script per object (indexes)

• SQL_ME_RICH

  SSChampion

  Points: 11200

  More actions

  February 9, 2016 at 5:24 pm

  #311327

  Hello -

  Have been looking all over for a solution that will allow me to script out my Non-Clustered Indexes to a single file for each index. The closest thing I have found was from Vince Panuccio (SmoDB version 1.0 Powershell script), but it does not generate a file for the NCI's.

  I wanted to see if I was to post it here, if someone might be able to either tell me how to modify it (to include the NCI's) or just show me a better example. Since this script belongs to someone else, I didn't know if it would be appropriate or not to include it here, but my hope is to find a solution (and I will forward the change on to them upon successfully doing so). Here is the Powershell script:

  ################################################################################################################################

  #

  # Script Name : SmoDb

  # Version : 1.0

  # Author : Vince Panuccio

  # Purpose :

  # This script generates one SQL script per database object including Stored Procedures,Tables,Views,

  # User Defined Functions and User Defined Table Types. Useful for versionining a databsae in a CVS.

  #

  # Usage :

  # Set variables at the top of the script then execute.

  #

  # Note :

  # Only tested on SQL Server 2008r2

  #

  ################################################################################################################################

  $server = "sandy"

  $database = "DBA"

  $output_path = "C:\SQL\Script"

  $schema = "dbo"

  $table_path = "$output_path\Table\"

  $storedProcs_path = "$output_path\StoredProcedure\"

  $views_path = "$output_path\View\"

  $udfs_path = "$output_path\UserDefinedFunction\"

  $textCatalog_path = "$output_path\FullTextCatalog\"

  $udtts_path = "$output_path\UserDefinedTableTypes\"

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

  $srv = New-Object "Microsoft.SqlServer.Management.SMO.Server" $server

  $db = New-Object ("Microsoft.SqlServer.Management.SMO.Database")

  $tbl = New-Object ("Microsoft.SqlServer.Management.SMO.Table")

  $scripter = New-Object ("Microsoft.SqlServer.Management.SMO.Scripter") ($server)

  # Get the database and table objects

  $db = $srv.Databases[$database]

  $tbl = $db.tables | Where-object { $_.schema -eq $schema -and -not $_.IsSystemObject }

  $storedProcs= $db.StoredProcedures | Where-object { $_.schema -eq $schema -and -not $_.IsSystemObject }

  $views = $db.Views | Where-object { $_.schema -eq $schema }

  $udfs = $db.UserDefinedFunctions | Where-object { $_.schema -eq $schema -and -not $_.IsSystemObject }

  $catlog = $db.FullTextCatalogs

  $udtts = $db.UserDefinedTableTypes | Where-object { $_.schema -eq $schema }

  # Set scripter options to ensure only data is scripted

  $scripter.Options.ScriptSchema = $true;

  $scripter.Options.ScriptData = $false;

  #Exclude GOs after every line

  $scripter.Options.NoCommandTerminator = $false;

  $scripter.Options.ToFileOnly = $true

  $scripter.Options.AllowSystemObjects = $false

  $scripter.Options.Permissions = $true

  $scripter.Options.DriAllConstraints = $true

  $scripter.Options.SchemaQualify = $true

  $scripter.Options.AnsiFile = $true

  $scripter.Options.SchemaQualifyForeignKeysReferences = $true

  $scripter.Options.Indexes = $true

  $scripter.Options.DriIndexes = $true

  $scripter.Options.DriClustered = $true

  $scripter.Options.DriNonClustered = $true

  $scripter.Options.NonClusteredIndexes = $true

  $scripter.Options.ClusteredIndexes = $true

  $scripter.Options.FullTextIndexes = $true

  $scripter.Options.EnforceScriptingOptions = $true

  function CopyObjectsToFiles($objects, $outDir) {

  if (-not (Test-Path $outDir)) {

  [System.IO.Directory]::CreateDirectory($outDir)

  }

  foreach ($o in $objects) {

  if ($o -ne $null) {

  $schemaPrefix = ""

  if ($o.Schema -ne $null -and $o.Schema -ne "") {

  $schemaPrefix = $o.Schema + "."

  }

  $scripter.Options.FileName = $outDir + $schemaPrefix + $o.Name + ".sql"

  Write-Host "Writing " $scripter.Options.FileName

  $scripter.EnumScript($o)

  }

  }

  }

  # Output the scripts

  CopyObjectsToFiles $tbl $table_path

  CopyObjectsToFiles $storedProcs $storedProcs_path

  CopyObjectsToFiles $views $views_path

  CopyObjectsToFiles $catlog $textCatalog_path

  CopyObjectsToFiles $udtts $udtts_path

  CopyObjectsToFiles $udfs $udfs_path

  Write-Host "Finished at" (Get-Date)

  I did try adding $index_path = "$output_path\index\", but to no avail.

  Many thanks in advance

• mister.magoo

  SSC-Forever

  Points: 47174

  More actions

  February 9, 2016 at 6:13 pm

  #1857568

  AFAIK, SMO doesn't treat indexes as objects, which is why you have scripting options to include them or not (in the table scripts).

  To get NCI into a separate file from the table using SMO, you would need to include them in the scripting options, then parse them out of the script generated for each table.

  It looks like the options are set to include them, so all you need is some code to strip them out of the Table scripts.

  MM
  

  ---

  
  select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

• Forum Etiquette: How to post Reporting Services problems
[/url]
• Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]
• How to Post Performance Problems - by Gail Shaw
[/url]