Export Row Counts From SQL using PowerShell

Export Row Counts From SQL using PowerShell

When I upgrade a NAV Database I like to export the row counts from the tables before and after the upgrade, and I like to compare the number of records. I wrote a PowerShell script to do this for me in one go.

The script is available on Github.

The script will retrieve the list of tables included in the database(s) and write the Row Count property into a csv file. It has two parameters, Databases which will take an array of strings and OutputFolder which will the folder where the script will create a CSV file for each database.

Executing the Script

To execute the script open SQL Management Studio  right click on the Server in the Object Explorer and choose the Start PowerShell option.

StartPowerShellSSMS

Execute the script using this syntax:


. "C:\YourFolder\Export-DatabaseRowCount-EXAMPLE.ps1"

SQLServerPowerShell

When the export start you will see the row counts and a progress bar:

ExportRowCount

Output

For this example I executed the script with two databases Demo Database NAV (10-0), and Demo Database NAV (9-0). The script produced two files Demo Database NAV (10-0).csv and Demo Database NAV (9-0).csv.

ExportRowCountOutput

To compare the counts now I can import them to an excel file and do a comparison with a VLOOKUP for example.

Script


function Export-DataRowCount
{
[CmdletBinding()]
param(
[parameter(Mandatory=$true)]
[array]$Databases,
[parameter(Mandatory=$true)]
[string]$OutputFolder
)

PROCESS
{
foreach($Databasein$Databases)
{
Write-Host$Database-ForegroundColor Green

$Step=0
$OutputFile="$($OutputFolder)\$($Database).csv"

# Create header
'Table, Number of Rows'|Add-Content$OutputFile

# Export Row Count
$Tables= dir ".\Databases\$($Database)\Tables\"
foreach($Tablein$Tables)
{
Write-Host$table.Name'Number of rows: '$table.RowCount-ForegroundColor Cyan
$Line="$($table.Name), $($table.RowCount)";
$Line|Add-Content$OutputFile;

# Progress Bar
$Step++;
$Percent=($Step/($Tables.Count/100));
Write-Progress-Activity $Table-Status "$($Percent) %"-PercentComplete $Percent
}

$Tables.Clear()
}
}
}

Example

. "C:\YourFolder\Export-DatabaseRowCount.ps1"

$DatabaseList = ('Demo Database NAV (10-0)', 'Demo Database NAV (9-0)')

Export-DataRowCount -Databases $DatabaseList `
                    -OutputFolder 'C:\Temp'
Advertisements

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s