Script Indexes for a Publication

I like to script the Indexes manually to apply to a subscriber when creating a publication.  Call me a sadist, but I am what I am.  I whipped up a quick script to connect to a published database, grab the articles in the publication and script out the indexes on said articles (obviously, not the clustered index).  It’s very very slow, but I’m too tired to try to optimize this now.  Use at your own risk.  Minimally tested and not a lot of error handling is involved.

	[string]$InstanceName = {throw "You must enter an Instance Name."},
	[string]$PubName = {throw "You must enter the name of the publication for which to script the indexes."},
	[string]$DistDBName = {throw "You must enter the name of the published database."},
	[string]$FileLocation = {throw "You must enter the location of where you want the scripts to be put."}

function ScriptIndexes([string]$TableName)
	$Table = Get-ChildItem | where {$_.Name -eq $TableName}
	foreach($ix in $Table.Indexes)
		if($ix.IsClustered -eq $false)
			$ix.Script() | out-file "$FileLocation\$($Table.Name).sql" -append; "GO`n$($separator)`n" | out-file "$FileLocation\$($Table.Name).sql" -append -Force

function Main()
	# Load SMO assembly, and if we're running SQL 2008 DLLs load the SMOExtended and SQLWMIManagement libraries
	$smo = [System.Reflection.Assembly]::LoadWithPartialName( 'Microsoft.SqlServer.SMO')
	if ((($smo.FullName.Split(','))[1].Split('='))[1].Split('.')[0] -ne '9') 
	  [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMOExtended') | out-null
	  [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SQLWMIManagement') | out-null

	# Handle any errors that occur
		# Handle the error
		$err = $_.Exception
		write-host $err.Message
		while( $err.InnerException ) 
			$err = $err.InnerException
			write-output $err.Message
		# End the script.

	$server = new-object ('Microsoft.SqlServer.Management.Smo.Server') $InstanceName

	$DistQuery = @"
SELECT AS TableName FROM dbo.sysarticles art (NOLOCK) 
INNER JOIN dbo.syspublications pub 
ON art.pubid = pub.pubid 
WHERE = '$PubName'

	$PubRes = invoke-sqlcmd -ServerInstance $InstanceName -Database $DistDBName -Query $DistQuery
	foreach($row in $PubRes)
		ScriptIndexes $row.TableName



Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: