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
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.
SirSQL.net 