Find Out Which Indexes are on which Filegroups using PowerShell And How To Find Other Information

A short post today to pass on a script I wrote to fulfil a requirement I had.

Which indexes are on which filegroups. I found a blog post showing how to do it with T-SQL but as is my wont I decided to see how easy it would be with PowerShell. I also thought that it would make a good post to show how I approach this sort of challenge.

I generally start by creating a SQL Server SMO Object You can use the SMO Object Model Diagram or Get-Member to work out what you need. As we are talking indexes and filegroups I will also create a Database object

$Server = "SQL2012Ser2012"
$DBName = "AdventureWorks2012"
$srv = New-Object Microsoft.SqlServer.Management.Smo.Server $Server
$DB = $srv.Databases[$DBName]

Then by piping the database object to Get-Member I can see the properties

Lets take a look at the table object in the same way

I can see the indexes object so I pipe that to Get-Member as well

Now I have enough to information to create the report. I will select the Name, Table, Type and Space Used of the Indexes and format them nicely

$Server = "SQL2012Ser2012"
$DBName = "AdventureWorks2012"
$srv = New-Object Microsoft.SqlServer.Management.Smo.Server $Server
$DB = $srv.Databases[$DBName]
$db.tables.Indexes|select Name,Parent,Filegroup,IndexType,SpaceUsed|Format-Table –AutoSize

and here are the results

However, you may want the results to be displayed in a different manner, maybe CSV,HTML or text file and you can do this as follows

$db.tables.Indexes|select Name,Parent,Filegroup,IndexType,SpaceUsed|ConvertTo-Csv c:\temp\filegroups.csv
Invoke-Item c:\temp\filegroups.csv

$db.tables.Indexes|select Name,Parent,Filegroup,IndexType,SpaceUsed| Out-File c:\temp\filegroups.txt
Invoke-Item c:\temp\filegroups.txt

$db.tables.Indexes|select Name,Parent,Filegroup,IndexType,SpaceUsed|ConvertTo-Html |Out-File c:\temp\filegroups.html
Invoke-Item c:\temp\filegroups.html

Hopefully this has shown you how easy it can be to use PowerShell to get all of the information that you need from your SQL Server and how to approach getting that information as well as several ways to display it