Of course, PowerShell excels at this. By using the SQL Server module, it’s really easy to:
- Connect to an instance and collect every user database, and
- From each database, collect every table, and
- For each table, collect row counts and space used, and
- If there are any indexes, group them, and sum their usage and report that as well
Here’s the script. Note that I have the server name hard-coded in there as localhost (more on that in a coming paragraph). Go ahead and take a look before we break it down.
Click through for the script, and homework is due next Tuesday on his desk.