Information Technology

PowerShell and SQL Server

Customers are always asking about using PowerShell to interact with their SQL servers.  One great source I’ve started following is Max Trinidad.  He has a very good grasp of both PowerShell and SQL Server administration.  Max has also contributed to codeplex with the SQLDevTools Module.  He wrote a blog postthat discusses some of the features.

Here’s one of the function that’s part of my recentSQLDevTools Module – “Get-TSQLTableRecordCount” which is a good example of a multi-purpose command.   The main purpose of this function is to assist in displaying the record count of all tables in the database.  But, at the same time it can assist you to provide a list of all users databases.

Usage #1  – To show all databases:

In case that you don’t remember  the database name in your SQL Server instance, this command give you the ability to list all databases.   Here’s an example:

PS> Get-TSQLTableRecordCount -SQLInstanceName MAX-PCWIN1 -ShowAllDatabases
Result:Processing SQLServer Instance: MAX-PCWIN1, DatabaseName: ALL

SQLServerName DatabaseName
————- ————
[MAX-PCWIN1]  AdventureWorks
[MAX-PCWIN1]  AdventureWorks2008R2
[MAX-PCWIN1]  Developer
[MAX-PCWIN1]  ReportServer
[MAX-PCWIN1]  ReportServerTempDB

Usage #2 – To Display all tables record counts in the selected Database:

Now you know the database name and you’re ready to use the command to get the record counts fo all your tables.  Here’s an example:

PS> Get-TSQLTableRecordCount -SQLInstanceName MAX-PCWIN1 -DatabaseName Developer

Processing SQLServer Instance: MAX-PCWIN1, DatabaseName: Developer

Parent      DisplayName                    RowCount DataSpaceUsed
——      ———–                    ——– ————-
[Developer] dbo.DatabaseDiskStats                 0             0
[Developer] dbo.DocRepository                     9          7568
[Developer] dbo.Employees                         9           240
[Developer] dbo.ServersToCheckDiskStats           0             0
[Developer] dbo.SurfSpotTable                     4             8
[Developer] Production.ProductDescription       762           144
[Developer] Production.ProductPhoto             101          2208
[Developer] Purchasing.PurchaseOrderDetail        0             0
[Developer] Sales.SalesPerson                     0             0