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