Powershell script - Get the total table count of all databases in multiple SQL Server Instances

To get the total table count of all databases in either one or more SQL Server Instances use this powershell script.

This basic  powershell script will give you the basic framework - which you adapt for your own purposes. For example - you may want to store this in a report or capture and place in some sort of  datawarehouse. 

This script - similar to  How to get SQL Server database object count using Powershell Measure-Object - 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"


$db_count_total=0



$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 = "
declare @sql nvarchar(max)

select @sql = coalesce(@sql + ' + ', '') + REPLACE('
 (select count(*)
 from ::DB::.sys.objects
 where is_ms_shipped = 0
   and type_desc = ''USER_TABLE'')', '::DB::', QUOTENAME(name))
from master.sys.databases

select @sql = 'select ' + @sql

exec (@sql)
"
$rdr = $sql.ExecuteReader()
$dt.Load($rdr)
$cn.Close()
 
}


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