Other: Obtaining Record Counts for all Tables

Other: Obtaining Record Counts for all Tables

The following script will count all records in all tables. 

  1. DECLARE
  2.     @TableRowCounts TABLE (
  3.             [TableName]                        VARCHAR(128),
  4.             [Action_Group]      INT,
  5.             [Incomplete]             INT,
  6.             [Complete]                INT,
  7.             [Total]                        INT
  8.       )
  9. ;
  10.   
  11. INSERT INTO
  12.     @TableRowCounts (
  13.             [TableName],
  14.             [Action_Group],
  15.             [Incomplete],
  16.             [Complete],
  17.             [Total]
  18.   )
  19.         EXEC sp_MSforeachtable '
  20. SELECT
  21.         ''?'' AS [TableName],
  22.         [Action_Group],
  23.         SUM(CASE WHEN Result_Id = '''' THEN 1 ELSE 0 END) AS [Incomplete],
  24.         SUM(CASE WHEN Result_Id = '''' THEN 0 ELSE 1 END) AS [Complete],
  25.         COUNT(*) as [Total]
  26. FROM ?
  27.         GROUP BY Action_Group
  28. ';
  29.  
  30. SELECT
  31.       Action_Group, TableName, Incomplete, Complete, Total
  32. FROM
  33.     @TableRowCounts
  34. WHERE 1=1
  35.         AND Total > 0
  36. ORDER BY
  37.         Action_Group,
  38.         [TableName]
  39.  
  40. GO


    • Related Articles

    • 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 ''?'' ...

    • 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 ...

    • 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 ...

    • 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 ...

    • 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 ...