How to get SQL Server database object count using Powershell Measure-Object

24 July,2019 by Jack Vamvas

Question: How can I iterate through a list of SQL Servers , extract the count of the databases , place into a powershell data table and get the database count sum for all the servers?

I'm having some trouble thinking about how to add up the counts from the individual servers into a total count , using powershell functions.

Answer: This basic  powershell script will give you the basic framework. 

This script iterates through a list of SQL Servers - listed in the file referenced in the $instancepath. The results of the command executed  are added to the DataTable . Once all the servers are touched and the results are placed in the DataTable .

The last line uses the Measure-Object cmdlet.  This cmdlet calculates the numeric properties of objects, and in this case, using the -Sum parameter will add the count numbers in the DataTable.

 

$instancepath="E:\health\SQL_Server\config\instances_all.txt"



$dt = new-object "System.Data.DataTable"
foreach ($instance in get-content $instancepath)
{
$instance
$cn = new-object System.Data.SqlClient.SqlConnection "server=$instance;database=master;Integrated Security=sspi"
$cn.Open()
$sql = $cn.CreateCommand()
$sql.CommandText = "select count(*) from sys.databases"
$rdr = $sql.ExecuteReader()
$dt.Load($rdr)
$cn.Close()
 
}


$dt | Measure-Object -Sum Column1 | out-host


Read More on Powershell Datatables

How to add a row in Powershell System.Data.Datatable (SQL Server ...

 

 


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on How to get SQL Server database object count using Powershell Measure-Object


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer