[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | out-null
<#
    Name:			Anthony E. Nocentino aen@centinosystems.com
    Date:			04/28/2015
    Description:	Script to move SQL Server data between filegroups
    
    Notes:			Does not migrate the following 
        			Partitioned tables
        			LOB objects
#>

$server = "."
$dbName = "TestDB"
$doWork = $TRUE  #safety net, true actually moves the data, false just outputs what the process will do
$onlineOpt = $TRUE  #request an online index move
$tablesToMove = "*"  # * is default, enter a matching string for example tableName*
$schemaToMove = "*"

$sqlServer = New-Object ('Microsoft.SqlServer.Management.Smo.Server') -argumentlist $server
$db = $sqlServer.Databases | Where-Object {$_.Name -eq $dbName}
$onlineIndex = $FALSE   #variable to hold status of sql editions capability for online index moves
$tableCount = 0 #simple counter for tables

if ($db.Name -ne  $dbName)
{
    Write-Output('Database not found')
    return
}

$fileGroup = Read-Host "Please enter the destination Filegroup"

$destFileGroup = ($db.FileGroups | Where-Object {$_.Name -eq $fileGroup} )

#check to see if the destination file group exists
if ( $destFileGroup.State -ne "Existing")
{
    Write-Output('Destination filegroup not found')
    return
}

Write-Output ('Database: ' +  $db.Name)

#if edition supports online indexing and the user requested it, turn it on
if ( $sqlServer.Information.EngineEdition -eq 'EnterpriseOrDeveloper' -and $onlineOpt -eq $TRUE )
{
    $onlineIndex = $TRUE
}

#all tables that are not paritioned, that meet our search criteria specified as cmd line parameters
$tables = $db.Tables | Where-Object {$_.Name -like $tablesToMove -and $_.Schema -like $schemaToMove -and $_.IsPartitioned -eq $FALSE}

$indexesToMove = @()
$heapsToMove = @()

#build a list of tables to be moved
foreach( $table in $tables ) 
{     
    #get a list of all indexes on this table
    $indexes = $table.Indexes 

    #iterate over the set of indexes
    foreach( $index in $indexes ) 
    {
       #if this table is a clustered or Non-Clustered index. Ignore special index types.
       if ( $index.IndexType -eq "ClusteredIndex" -or $index.IndexType -eq "NonClusteredIndex" ) 
       {
           if ( $index.FileGroup -ne $fileGroup )
           {
             Write-Output( $table.Schema + '.' + $table.Name + " " + $index.Name)
             $tableCount++
             $indexesToMove += $index
           }
       }
    }
    if ($table.HasClusteredIndex -eq $FALSE)
    {
        Write-Output( $table.Schema + '.' + $table.Name + " as a heap")
        $tableCount++
        $heapsToMove += $table
    }
}

#confirmation of the move request
$confirmation = Read-Host "Are you sure you want to move the" $tableCount "objects listed above to the destination filegroup? (y/n)"
if ($confirmation -ne 'y') 
{
    Write-Output('No tables moved')
    return
}

foreach( $index in $indexesToMove ) 
{
    try
    {
        Write-Output ('Moving: ' + $index.Name)
        $index.FileGroup = $fileGroup

        if ( $doWork -eq $TRUE ) 
        {
            $index.Recreate()
        }
    }
    catch
    {
        Write-Output ('Failed moving index ' + $index + ' to ' + $fileGroup + ' ' + $error[0].Exception.InnerException )
        return
    }
}#end for each index

    #if we didn't find a clustered index after looking at all the indexes, it's a heap. Let's move that too
    <#
        our algortihm is as follows
        Find the leading column on the table

        Instantiate a new Smo.Index object on the table named "TempIndex"
        Instantiate a new Smo.IndexesColumn object on leading column and add it to our TempIndex
        Set the Index as IsClustered
        Add the column to the index
        Create the Index
        Use DropAndMove to move the index to the destination filegroup and leave it as a heap

    #>
foreach($table in $heapsToMove)
{
    $cols = $table.Columns[0]
    $leadingCol = $cols[0].Name
    
    $idx = New-Object -TypeName Microsoft.SqlServer.Management.SMO.Index -argumentlist $table, "TempIndex"
    $icol1 = New-Object -TypeName Microsoft.SqlServer.Management.SMO.IndexedColumn -argumentlist $idx, $leadingCol, $true
    $idx.IsClustered = $TRUE
    $idx.IndexedColumns.Add($icol1)

    #Write-Output $idx.Script()

    #check to see if the table is not already in the destionation filegroup and the table is indexable
    if ( $table.FileGroup -ne $fileGroup -and $table.IsIndexable -eq $TRUE) 
    {
        try
        {
            Write-Output('Moving Heap: ' + $table.Name)
            if ( $doWork -eq $TRUE ) 
            {
                $idx.Create()
                $idx.DropAndMove($fileGroup)
            }
        }
        catch
        {
            Write-Output('Failed moving heap: ' + $table + ' to ' + $fileGroup + ' ' + $error[0].Exception.InnerException )
            Write-Output('Remove any Tempory indexes created')
            return
        }
    }
    else 
    {
        Write-Output($table.Name + ' is already in destination filegroup')
    }
}

#spit out data about data file size and allocation
$db.Refresh()
$tables.Refresh()

$dbfileGroup = $db.FileGroups 
Write-Output('Filegroup contents')
Write-Output($dbfileGroup.Files | Sort-Object -Property ID |  Format-Table Parent, ID, FileName, Name, Size, UsedSpace)

Write-Output('Tables')
Write-Output($tables | Select Parent, Schema, Name, FileGroup | Format-Table )