Grabbing Table Row Counts Into SQL Using PowerShell

Sometimes Twitter gives you the impetus to get something done that you’ve been meaning to accomplish but just haven’t quite gotten around to. Yesterday was one of the days that happened.

Ed Watson (blog|twitter)  asked the following question on #SQLHelp

SQLHelp

 

This is something I’ve been meaning to do for a while so that I can get good ongoing trending information around table row counts.

There are several ways to accomplish this but PowerShell was the clear winner for me. I wanted the script to accomplish a couple of things

  • Capture the SQL instance name, database name, table name (including schema) and row count for all tables in a database
  • To grab the information for all the databases on a server (including system databases)
  • To write the data to a SQL table
  • Provide the option of dumping the data to the screen
  • Allow me to execute it quickly and easily against multiple servers without providing interactive input

 

The script I’ve written does all this. The only bugs I’ve found are that the model and tempdb table information is not accessible, but this isn’t really an issue as model doesn’t get any use and tempdb is far too frequently updated to be of any use.

 

The Scripts

Firstly we need to create a couple of SQL tables to hold the results. These should be created on the SQL instance and database of your choosing. The script itself should be updated to reference the locations of these tables (ultimately it made more sense to do it this way rather than pass the information in as a parameter).

 

CREATE TABLE dbo.SQLTableRowCounts

    (

      SQLInstance NVARCHAR(128) ,

      DBName NVARCHAR(128) ,

      TableName NVARCHAR(256) ,

      TableRowCount BIGINT, 

      LoadDate DATE DEFAULT CONVERT(DATE, GETDATE())

    );

    

CREATE TABLE dbo.SQLTableRowCountsArchive

    (

      SQLInstance NVARCHAR(128) ,

      DBName NVARCHAR(128) ,

      TableName NVARCHAR(256) ,

      TableRowCount BIGINT,

      LoadDate DATE DEFAULT CONVERT(DATE, GETDATE())

    );    

 

There are no indexes on the tables currently. Depending upon your usage patterns a couple of different indexes could apply. I’ll let you decide what works best for you.

 

The PowerShell script is quite well documented and includes execution examples (you’ll want to run this on a machine where the SQL PS providers have been installed).

<#

.SYNOPSIS

   Script takes in a list of SQL Instances and iterates through the databases to retrieve a row count for each table. This is then loaded into a SQL database.

   Within the script the $StorageSQLServer and $StorageDatabase should be updated to reflect the relevant locations to load the data.

.DESCRIPTION

   Script takes in a list of SQL Instances and iterates through the databases to retrieve a row count for each table. This is then loaded into a SQL database.

   Within the script the $StorageSQLServer and $StorageDatabase should be updated to reflect the relevant locations to load the data.

   Requires bulk admin permissions on the SQL Server that you are loading the data to.

   

   Accepts pipelined inputs so that you can pass an array for working with multiple SQL Instances

.PARAMETER <$SQLInstance>

   This is the SQL Instance that you'll be working with

.PARAMETER <$verbose>

    Initially set to false. When set to true will also output the data to the console

.EXAMPLE

   Basic example: Get-TableRowCounts MySQLInstance

   Advanced: Get-Content C:TempSQLInstances.txt | % { Get-TableRowCounts $_ }

   

#>

 

param

(

[string]$SQLInstance = "$(Read-Host 'SQL Server Instance [e.g. SQLPROD]')",

[switch]$verbose = $false

)

 

#Load the SQL snapins

get-pssnapin | where {$_.Name -match "SqlServerProviderSnapin100"} 

if (!$CheckSQLSnapins) 

{

    Add-PSSnapin SqlServerProviderSnapin100 

    Write-Host "SqlServerProviderSnapin100 Loaded"

}

 

#Set the following to the SQL instance and database that you want to load the data to

#You will need to have bulk insert permissions to be able to perform this action

$StorageSQLServer = "localhost"

$StorageDatabase = "SQLMonitor"

 

$BCPconnectionString = "Data Source=$StorageSQLServer;Integrated Security=true;Initial Catalog=$StorageDatabase;"

 

#Clean out the current data table if the data is not from today

Invoke-Sqlcmd -ServerInstance $StorageSQLServer -Database $StorageDatabase -Query "DELETE FROM dbo.SQLTableRowCounts WHERE LoadDate != CONVERT(DATE, GETDATE());"

 

 

 

function TableList ($SQLInstance, $DBName)

{

 

    #Handle default vs named instances

    if ($SQLInstance.Contains("`"))

    {        $location = "SQLSERVER:SQL$SQLInstanceDatabases$DBNameTables" }

    else

    {        $location = "SQLSERVER:SQL$SQLInstanceDEFAULTDatabases$DBNameTables" }

 

    <#

    Create data table to hold the results

    The datatable will live for the life of the function call to reduce memory overhead

    pulling from multiple servers

    #>

    $dataTable = New-Object system.Data.DataTable "Results"

    #Specify the column names for the data table

    $col1 = New-Object system.Data.DataColumn SQLInstance, ([string])

    $col2 = New-Object system.Data.DataColumn DatabaseName, ([string])

    $col3 = New-Object system.Data.DataColumn FullTableName,([string])

    $col4 = New-Object system.Data.DataColumn RowCounts,([int64])

 

    #Add the columns to the data table

    $dataTable.Columns.Add($col1)

    $dataTable.Columns.Add($col2)

    $dataTable.Columns.Add($col3)

    $dataTable.Columns.Add($col4)

 

    #Get our table list

    $TableList = dir $location -Force | select Owner, Name, RowCount

 

    #Iterate through the tables, load the rowcounts into the datatable

    foreach ($TableInfo in $TableList)

    {

        $TableOwner = $TableInfo.Owner 

        $TableName = $TableInfo.Name

        $TableRowCount = $TableInfo.RowCount

 

        $TableFullName = "$TableOwner.$TableName"

 

        #Provided there are rows in the table

        #Load the table/rowcounts into the datatable

        #This prevents errors due to nulls in model & tempdb

        if ($TableRowCount)

        {

            $row = $dataTable.NewRow()

            $row.SQLInstance = $SQLInstance

            $row.DatabaseName = $DBName

            $row.FullTableName = $TableFullName

            $row.RowCounts = $TableRowCount

            $dataTable.Rows.Add($row)

        }

 

    }

 

    if ($verbose) { Write-Output $dataTable }

 

    #Bulk load the data into SQL from the data table

    $bulkCopyRoles = new-object ("Data.SqlClient.SqlBulkCopy") $BCPconnectionString

    #Loads to the current table

    $bulkCopyRoles.DestinationTableName = "SQLTableRowCounts"

    $bulkCopyRoles.WriteToServer($dataTable)

    #Loads to the archive table

    $bulkCopyRoles.DestinationTableName = "SQLTableRowCountsArchive"

    $bulkCopyRoles.WriteToServer($dataTable)

 

    #Clean up after ourselves so we release memory used to hold the data table

    Remove-Variable dataTable

}

 

 

function DBList ($SQLInstance)

{

 

    #Handle default vs named instances

    if ($SQLInstance.Contains("`"))

    {        $BaseLocation = "SQLSERVER:SQL$SQLInstanceDatabases" }

    else

    {        $BaseLocation = "SQLSERVER:SQL$SQLInstanceDEFAULTDatabases" }

 

    $dblist = dir $BaseLocation -Force | select Name

    foreach ($db in $dblist)

    {

        $DBName = $db.Name

        TableList $SQLInstance $DBName 

    }

}

 

DBList $SQLInstance 

 

 

This script could easily be setup to execute daily to load the data into SQL so that you can perform quick and easy trends on table growth.

 

Give it a try, let me know what you think.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s