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

image

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

image

 

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

image

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

image

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

image

 

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

image

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

image

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

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