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

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. Let us see how we can find Powershell Index Size.

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

Solarwinds

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

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.

SQL SERVER - Size of Index Table for Each Index - Solution 3 - Powershell Index Size powershell

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

Solarwinds
, , ,
Previous Post
SQL SERVER – Size of Index Table for Each Index – Solution 2
Next Post
SQL SERVER – Four Posts on Removing the Bookmark Lookup – Key Lookup

Related Posts

3 Comments. Leave new

  • Laerte Junior
    May 10, 2010 7:16 am

    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 !!!

    Reply
  • I have reading up on Powershell as and when possible, it is really powerful to do some database related activities. There are lot of powershell projects on codeplex, SQLPSX is a really cool powershell library, idera also has a full fledged powershell editor with intellisense and free sample scripts.

    Reply
  • Demétrio Silva
    May 11, 2010 11:03 pm

    Great Laertes,

    Compliments Pinal huh? Excellent article

    Hugs,

    Demétrio Silva

    Reply

Leave a Reply

Menu