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.

param(
	[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
	Trap 
	{
		# Handle the error
		$err = $_.Exception
		write-host $err.Message
		while( $err.InnerException ) 
		{
			$err = $err.InnerException
			write-output $err.Message
		};
		# End the script.
		break
	}

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

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

	$PubRes = invoke-sqlcmd -ServerInstance $InstanceName -Database $DistDBName -Query $DistQuery
	sl SQLSERVER:SQL\$InstanceName\DATABASES\$DistDBName\TABLES
	foreach($row in $PubRes)
	{
		ScriptIndexes $row.TableName
	}	
}

cls
Main

Advertisements

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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: