PowerShell & SQL Server: Search & Find Indexes By SchemaName, TableName, IndexName Or ColumnName Across Instances/DB’s

Necessity is the mother of new PowerShell code :-)! Today, I received an email request to get the list of all indexes in a given TableName but the requester did not specify the DatabaseName although the SQL InstanceName was specified. It would be too easy to ask the person for the DBName and script it out in SSMS but that is not what a PowerShell person would/should really do!

The requirements:

Given a list of SQL Instances, find indexes in non-heap tables matching the below criteria:

  • searches given databases (optional, all databases if none specified)
  • a schema name (optional, like/exact can be done)
  • a matching table name (optional, like/exact can be done)
  • a matching index name (optional, like/exact can be done)
  • a matching column name in index columns (optional, like/exact can be done)
  • If nothing is specified, lists all indexes in all non-heap tables of all databases in all instances

I looked at dbatools for a function named Find-DbaDbIndex that would do the above but did not. I plan to contribute this to dbatools but consider this an early version for your use now.

Some usage examples:

Actual code examples are part of the code below but here are a few scenarios

  • For SchemaName, TableName, IndexName & ColumnNames use LIKE type search or EXACT search or a mix for the four
  • Get all indexes that are in Schema “Customer” across SQLInstance1, 2 and 3.
  • Get all indexes using the column with the name SSN or a variation of that like CUST_SSN
  • Get all indexes in all tables that have the string “SECURITY” or “SEC” in it
  • Get all indexes in table Customer that use the column CustomerID in index
  • With no knowledge where of a table named CUST_SECURITY is, search across all databases of given instances and return all the indexes of matching table(s)
  • Look across all databases for indexes using column “CriticalId” and get the fragmentation percent
  • Look at indexes in system databases like master, msdb, model and tempdb

Prerequisites:

You need the all-encompassing dbatools PowerShell Module for SQL Server. If you don’t have it already, simply install it by running:

Install-Module dbatools;

Code:

The code (Find-DbaDbIndex) is also available on GitHub at on my dbatools fork. I plan get it integrated into the dbatools module and make a pull request at which point, it will be part of dbatools in the future. For now, I have created a GIT gist and referenced the link to embed GitHub code in this blog post.

Hope this helps you find your indexes fast and easy! As always, your feedback is welcome. If you wish to improve this, you can directly do so once I submit this code to dbatools!

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