Skip to content

February 18, 2013

Get Table/Index Filegroup Listing using Powershell

by Scott Newman

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 
Read more from Powershell, SQL Server

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 )

Google+ photo

You are commenting using your Google+ 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 )

Connecting to %s

Note: HTML is allowed. Your email address will never be published.

Subscribe to comments

%d bloggers like this: