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.             [Batch_Number]      INT,
  5.             [Incomplete]             INT,
  6.             [Complete]                INT,
  7.             [Total]                        INT
  8.       )
  9. ;
  10.   
  11. INSERT INTO
  12.     @TableRowCounts (
  13.             [TableName],
  14.             [Batch_Number],
  15.             [Incomplete],
  16.             [Complete],
  17.             [Total]
  18.   )
  19.         EXEC sp_MSforeachtable '
  20. SELECT
  21.         ''?'' AS [TableName],
  22.         [Batch_Number],
  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 Batch_Number
  28. ';
  29.  
  30. SELECT
  31.       Batch_Number, TableName, Incomplete, Complete, Total
  32. FROM
  33.     @TableRowCounts
  34. WHERE 1=1
  35.         AND Total > 0
  36. ORDER BY
  37.         Batch_Number,
  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 ...

    • Other: Global SQL Search

      The following query will create a stored procedure named SearchAllTables which will let you search all tables in the database for a value. It can be used the following ways: --Search for a column that exactly contains 'Test': exec SearchAllTables ...