SQL SERVER – Size of Index Table for Each Index – Solution 3 – Powershell
- by pinaldave
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
Reference: Pinal Dave (http://blog.sqlauthority.com)
Filed under: Pinal Dave, SQL, SQL Authority, SQL Index, SQL Query, SQL Scripts, SQL Server, SQL Tips and Tricks, T SQL, Technology Tagged: Powershell
