SQL SERVER – Size of Index Table for Each Index – Solution 3 – Powershell

Laerte Junior
Laerte Junior

If you are a Powershell user, the name of the Laerte Junior is not a new name. He is the one man with exceptional knowledge of Powershell. He is not only very knowledgeable, but also very kind and eager to those in need. I have been attempting to setup Powershell for many days, but constantly facing issues. I was not able to get going with this tool. Finally, yesterday I sent email to Laerte in response to his comment posted here. Within 5 minutes, Laerte came online and helped me with the solution. He spend nearly 15 minutes working along with me to solve my problem with installation. And yes, he did resolve it remotely without looking at my screen – What a skilled and exceptional person!! I will soon post a detail note about the issue I faced and resolved with the help of Laerte.

Here is his solution to my earlier puzzle in his own words. Read the original puzzle here and Laerte’s solution from here.

Hi Pinal,

I do not say better, but maybe another approach to enthusiasts in powershell and SQLSPX library would be:

1 – All indexes in all tables and all databases
Get-SqlDatabase -sqlserver “Yourserver” | Get-SqlTable | Get-SqlIndex | Format-table Server,dbname,schema,table,name,id,spaceused

2 – All Indexes in all tables and specific database
Get-SqlDatabase -sqlserver “Yourserver” “Yourdb” | Get-SqlTable | Get-SqlIndex | Format-table Server,dbname,schema,table,name,id,spaceused

3 – All Indexes in specific table and database
Get-SqlDatabase -sqlserver “Yourserver” “Yourdb” | Get-SqlTable “YourTable” | Get-SqlIndex | Format-table Server,dbname,schema,table,name,id,spaceused

and to output to txt.. pipe Out-File

Get-SqlDatabase -sqlserver “Yourserver” | Get-SqlTable | Get-SqlIndex | Format-table Server,dbname,schema,table,name,id,spaceused | out-file c:\IndexesSize.txt

If you have one txt with all your servers, can be for all of them also.

Lets say you have all your servers in servers.txt: something like
NameServer1
NameServer2
NameServer3
NameServer4

We could Use :
foreach ($Server in Get-content c:\temp\servers.txt) {
Get-SqlDatabase -sqlserver $Server | Get-SqlTable | Get-SqlIndex | Format-table Server,dbname,schema,table,name,id,spaceused
}

:)

After fixing my issue with Powershell, I ran Laerte‘s second suggestion – “All Indexes in all tables and specific database” and found the following accurate output.

Laerte Junior
Laerte Junior

If you are a Powershell user, the name of the Laerte Junior is not a new name. He is the one man with exceptional knowledge of Powershell. He is not only very knowledgeable, but also very kind and eager to those in need. I have been attempting to setup Powershell for many days, but constantly facing issues. I was not able to get going with this tool. Finally, yesterday I sent email to Laerte in response to his comment posted here. Within 5 minutes, Laerte came online and helped me with the solution. He spend nearly 15 minutes working along with me to solve my problem with installation. And yes, he did resolve it remotely without looking at my screen – What a skilled and exceptional person!! I will soon post a detail note about the issue I faced and resolved with the help of Laerte.

Here is his solution to my earlier puzzle in his own words. Read the original puzzle here and Laerte’s solution from here.

Hi Pinal,

I do not say better, but maybe another approach to enthusiasts in powershell and SQLSPX library would be:

1 – All indexes in all tables and all databases
Get-SqlDatabase -sqlserver “Yourserver” | Get-SqlTable | Get-SqlIndex | Format-table Server,dbname,schema,table,name,id,spaceused

2 – All Indexes in all tables and specific database
Get-SqlDatabase -sqlserver “Yourserver” “Yourdb” | Get-SqlTable | Get-SqlIndex | Format-table Server,dbname,schema,table,name,id,spaceused

3 – All Indexes in specific table and database
Get-SqlDatabase -sqlserver “Yourserver” “Yourdb” | Get-SqlTable “YourTable” | Get-SqlIndex | Format-table Server,dbname,schema,table,name,id,spaceused

and to output to txt.. pipe Out-File

Get-SqlDatabase -sqlserver “Yourserver” | Get-SqlTable | Get-SqlIndex | Format-table Server,dbname,schema,table,name,id,spaceused | out-file c:\IndexesSize.txt

If you have one txt with all your servers, can be for all of them also.

Lets say you have all your servers in servers.txt: something like
NameServer1
NameServer2
NameServer3
NameServer4

We could Use :
foreach ($Server in Get-content c:\temp\servers.txt) {
Get-SqlDatabase -sqlserver $Server | Get-SqlTable | Get-SqlIndex | Format-table Server,dbname,schema,table,name,id,spaceused
}

:)

After fixing my issue with Powershell, I ran Laerte‘s second suggestion – “All Indexes in all tables and specific database” and found the following accurate output.

Click to Enlarge
Click to Enlarge

Reference: Pinal Dave (http://blog.sqlauthority.com)

Click to Enlarge
Click to Enlarge

Reference: Pinal Dave (http://blog.sqlauthority.com)

About these ads

5 thoughts on “SQL SERVER – Size of Index Table for Each Index – Solution 3 – Powershell

  1. Pinal,

    Really Thanks for the nice words, and if I can help you as you have done (and does) for me many times with your blog posts, it is an honor for me.

    Count on me anytime my friend :)

    Thanks !!!

    Like

  2. Pingback: SQLAuthority News – Monthly Roundup of Best SQL Posts Journey to SQL Authority with Pinal Dave

  3. Pingback: SQL SERVER – Weekly Series – Memory Lane – #004 « SQL Server Journey with SQL Authority

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