Audit Scripts: Row Counts and Progress Monitoring

Audit Scripts: Row Counts and Progress Monitoring

This article shows you a script that can be used to monitor the progress of both a Backup and a Restore.  It can also be used to just review row counts on all the Universal Database tables.

The first code block is used to populate a SQL Server Temp table with summary data from every table in the database.  Below is a description of each field.  

  • tableName - This is the name of the table.
  • numRows - This is the number of rows for each group of records.  The following three fields group the row count of a table.
  • batchNumber - This indicates the batch number.  For example if a table has 10,000 total rows and you're working on an injection.  5,000 could be batch number 0 indicating they haven't started restoring yet, 4,500 could be batch number 10 indicating that they were successfully restored, and 500 might be batch number 1 indicating that there could be an error restoring those records.
  • Final_Status - This is the value in the Final_Status column from the table.
  • loadStatus - This will indicate 'Loaded' for rows that have a Result_Id and 'Not Loaded' for rows that do not have a Result_Id
  • minDate - This is the oldest date/time in the Status_Date_From column of the table for that group of records.
  • maxDate - This is the most recent date/time in the STatus_Date_Till column of the table for that group of records.
  1. USE [{database Name Here }];
  2. GO
  3. IF OBJECT_ID('tempdb..#Tbl', 'U') IS NOT NULL
  4. BEGIN
  5. -- Temp Table exists, so DROP it
  6. DROP TABLE #Tbl;
  7. END

  8. CREATE TABLE 
  9. #Tbl 
  10. (
  11. tableName NVARCHAR(100) NOT NULL, 
  12. numRows BIGINT NULL,
  13. batchNumber BIGINT NULL, 
  14. Final_Status nvarchar(50) NULL,
  15. loadStatus nvarchar(20) NULL, 
  16. minDate datetime NULL, 
  17. maxDate datetime NULL
  18.   )

  19. INSERT INTO #Tbl (tableName, batchNumber, Final_Status, loadStatus, minDate, maxDate, numRows)
  20. EXEC sp_MSforeachtable
  21. '
  22. SELECT
  23. ''?'',
  24. Batch_Number,
  25. Final_Status,
  26. IIF(Result_Id = '''', ''Not Loaded'', ''Loaded''),
  27. MIN([Status_Date_From]),
  28. MAX([Status_Date_Till]),
  29. COALESCE(COUNT([Row]), 0)
  30. FROM
  31. ?
  32. GROUP BY 
  33. Batch_Number,
  34. Final_Status,
  35. IIF(Result_Id = '''', ''Not Loaded'', ''Loaded'')
  36. '

Reviewing the results loaded in the Temp Table

Below are some examples of how you can use the summary data that the previous script loads to the temp table.  The queries shown below must be executed in the same Query Window in SQL Server Management Studio (SSMS) because SSMS temp tables have local scope to the query window in which they were created.

  1. SELECT
  2. *
  3. FROM
  4. #Tbl
  5. ORDER BY
  6. tableName,
  7. loadStatus,
  8. batchNumber,
  9. Final_Status

Below are sample results of the query above.



This sample query was run on a database after injecting data.  You can see that the data is already loaded.  Looking at __M_Contacts you can see that there are 5,368 records that are 'Active', and 1,387 that are 'Archived' and all 6,755 have been loaded to the destination system as indicated by the loadStatus column.

Data Summary

This one is very useful for summarizing the data stored in the project database.  This can be saved in your playbook and your notes for later reference.

  1. SELECT
  2. CONCAT('Total Rows', ' - ', FORMAT(SUM(numRows),'#,###'))
  3. FROM
  4. #Tbl
  5. WHERE 1=1
  6. AND tableName !='[dbo].[__M__READ_ME_FIRST]'

  7. UNION ALL

  8. SELECT
  9. CONCAT('Active Users - ', SUM(numRows))
  10. FROM
  11. #Tbl
  12. WHERE 1=1
  13. AND tableName = '[dbo].[__M_Users]'
  14. AND Final_Status = 'Active'

  15. UNION ALL

  16. SELECT
  17. CONCAT(tableName, ' - ', FORMAT(SUM(numRows),'#,###'))
  18. FROM
  19. #Tbl
  20. WHERE 1=1
  21. AND tableName !='[dbo].[__M__READ_ME_FIRST]'
  22. GROUP BY
  23. tableName

Progress monitoring

The following query can be used to monitor the progress of injecting data to your destination system.

  1. SELECT 
  2.             tableName,
  3.             SUM(IIF( loadStatus = 'Loaded', numRows,0)) loadedCount,
  4.             SUM(IIF( loadStatus = 'Loaded', 0,numRows)) remainingCount,
  5.             SUM(numRows) totalRows,
  6.             ROUND(CAST(SUM(IIF( loadStatus = 'Loaded', numRows,0)) AS FLOAT)  * 100 / SUM(numRows),2) percComplete,
  7.             ROUND(CAST(SUM(IIF( loadStatus = 'Loaded', 0, numRows)) AS FLOAT)  * 100 / SUM(numRows),2) percRemaining
  8. FROM 
  9.             #Tbl 
  10. WHERE 1=1
  11.             AND Final_Status !='Deleted'
  12. GROUP BY 
  13.             tableName

Below is a description of each column of output for the above query and a screenshot of example output.

  • tableName - The name of the table.
  • loadedCount - The number of rows that have a Result_Id, which means they've been injected to the destination system.
  • remainingCount - The number of rows that do not have a Result_Id.
  • totalRows - The total number of rows on that table that are not in 'Deleted' status.
  • percComplete - The percentage of rows that have a Result_Id
  • percRemaining - The percentage of rows that do NOT have a Result_Id

More Examples

Active Users

  1. SELECT
  2. SUM(numRows)
  3. FROM
  4. #Tbl
  5. WHERE 1=1
  6. AND Final_Status = 'Active'
  7. AND tableName = '[dbo].[__M_Users]'

Total Record Count

  1. SELECT
  2. SUM(numRows)
  3. FROM
  4. #Tbl


Document Count
  1. SELECT
  2. SUM(numRows)
  3. FROM
  4. #Tbl
  5. WHERE 1=1
  6. AND tableName = '[dbo].[__M_DocumentFiles]'


Database Summary
  1. SELECT
  2.         * 
  3. FROM (
  4.                         SELECT 
  5.                                     tableName, 
  6.                                     SUM(numRows) numRows 
  7.                         FROM 
  8.                                     #Tbl 
  9.                         GROUP BY 
  10.                                     tableName

  11.                         UNION

  12.                         SELECT 
  13.                                     '[__Total_Rows_All_Tables]', 
  14.                                     SUM(numRows) 
  15.                         FROM 
  16.                                     #Tbl 

  17.                         UNION

  18.                         SELECT 
  19.                                     '[__Active Users]', 
  20.                                     SUM(numRows) 
  21.                         FROM 
  22.                                     #Tbl 
  23.                         WHERE 1=1
  24.                                     AND Final_Status = 'Active'
  25.                                     AND tableName = '[dbo].[__M_Users]'
  26.                 ) t 
  27. ORDER BY 
  28.                     tableName


    • Related Articles

    • Audit Scripts: Activity Billed/Unbilled row counts

      After you have backed up financial information like Time Entries, Expense Entries, and Fee Entries it is a good idea to audit the results prior to restoring data to the destination system. For example, some Universal Destinations support restoring ...
    • Audit Scripts: General Audit

      After completing a backup there are a number of things that are good to review prior to restoring into the destination system. The script below reviews several of the data points that should be looked at prior to restoring. This script is not ...
    • Other: Total Record Counts

      The following SQL script will count all records in all tables in a database. DECLARE @TableRowCounts TABLE ([TableName] VARCHAR(128), [RowCount] INT) ; INSERT INTO @TableRowCounts ([TableName], [RowCount]) EXEC sp_MSforeachtable 'SELECT ''?'' ...
    • Other: Obtaining Record Counts for all Tables

      The following script will count all records in all tables. DECLARE @TableRowCounts TABLE ( [TableName] VARCHAR(128), [Batch_Number] INT, [Incomplete] INT, [Complete] INT, [Total] INT ) ; INSERT INTO @TableRowCounts ( [TableName], [Batch_Number], ...
    • Other: Creating Unique Display Numbers

      The following script will create unique Final_ReferenceCode for all Contacts. It can be easily modified to apply to other record types. Please note that the Final_ReferenceCode that are generated are based on the data in the database. If you are ...