Get Table/Index Filegroup Listing using Powershell

Quick script to get a listing of tables\indexes and what filegroup said object belongs to via powershell.  This returns an array of PSObject.  The tables\indexes can be filtered via the ObjectType in the psobject array.  As always, this uses SQLPSX.

Import-Module SqlServer

$outputs = @();

Get-SqlDatabase -dbname 'database' -sqlserver 'server' | %{
	$db = $_
	Get-SqlTable -Database $_ | %{
		
		$output = New-Object -TypeName PSObject -Property @{
			ObjectName = $_.Name
			FileGroupName = $_.FileGroup
			ObjectType = "Table"
		}
		$outputs += $output
		
		$_.Indexes | %{
			$output = New-Object -TypeName PSObject -Property @{
				ObjectName = $_.Name
				FileGroupName = $_.FileGroup
				ObjectType = "Index"
			}
			$outputs += $output
		}
	}
	Get-SqlView -Database $_ | %{
		$vw = $_
		if($_.HasIndex)
		{
			$_.Indexes | %{
				$output = New-Object -TypeName PSObject -Property @{
					ObjectName = $_.Name
					FileGroupName = $_.FileGroup
					ObjectType = "Index"
				}
				$outputs += $output
			}
			
		}
	}
}

$outputs 
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: