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

25 July,2019 by Jack Vamvas

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


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 Powershell script - Get the total table count of all databases in multiple SQL Server Instances


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