Chapter 25. Inventory database table statistics using PowerShell and SQL Server Management Objects
SQL Server Management Objects, or SMO, were the only way to interact with SQL Server in the PowerShell v1 days. Although the release of the SQL Provider simplified interacting with SQL Server from PowerShell, there are still many reasons to understand and use SMO with PowerShell. Performance increases, finely tuned control, and access to a large base of existing code samples are but a few of the benefits to understanding and using SMO.
In this chapter you’ll create a script to inventory all of the databases on an instance of a SQL Server. The script will query the databases to get statistics for each table in each database—things such as row counts, data space used, and index space used. It will take this data and insert it into a new database named PSMVP, which the script will create if it’s not present. In order to achieve the desired results you need to start with a brief overview of SMO.
SMO is a set of .NET Framework libraries—dynamic-link libraries (DLLs) you can use from PowerShell. These libraries expose a set of classes from which you can create various objects. If you were to look at the Object Explorer in SQL Server Management Studio you’d soon notice parallels in the SMO libraries. Almost every object in the tree has a corresponding class in SMO.
