Search a database for a value and count matching rows

What?
We have a datawarehouse and we want to be able to count all the records in any table of the database that match on a particular warehouse load. This is a column value where the column is called "WarehouseLoadKey" and the value we want to search on is "3" (the 3rd incremental load).

How?
The below stored procedure can be reduced to just a script as long as you declare and set the parameters after the BEGIN and extract the script from BEGIN to END (excluding the words BEGIN and END - avoids the need to create a stored procedure and saving it on a database):
copyraw
IF OBJECT_ID('usp_CountRecordsPerTablePerColumn', 'P') IS NOT NULL
    DROP PROCEDURE [usp_CountRecordsPerTablePerColumn];
GO

CREATE PROCEDURE [usp_CountRecordsPerTablePerColumn]
(
        @p_Value int,
        @p_Column varchar(max)
)
AS
/***********************************************************************************
** Author:      Joel Lipman                                                       **
** Created On:  29 October 2012                                                   **
** Revision:    v1.0                                                              **
** Description: Stored Procedure to return counts of records per staging database **
**              table that contains a specific value.                             **
**              NOTE: This script can be run separate to a stored procedure by    **
**                    declaring @p_Value and @p_Column and using only the code    **
**                    from the BEGIN to the END (exclusive) - allows you to run   **
**                    this against any table only changing the parameter column.  **
**                                                                                **
** Usage:                                                                         **
**    EXEC [usp_CountRecordsPerTablePerColumn] '3', 'Customer_ID';                **
**                                                                                **
** Parameters :                                                                   **
**    - Value:  the value you want to match on (eg. Customer_ID)                  **
**    - Column: the column name which will contain this value.                    **
**                                                                                **
** Return Values : Table of three columns:                                        **
**    - myValue: value specified in parameter 1 (matched in database to search)   **
**    - myTable: table in which the column occurs                                 **
**               (returned as "database.schema.tablename")                        **
**    - myCount: the number of records matching the value in this table.          **
**                                                                                **
************************************************************************************
** Change History:                                                                **
************************************************************************************
** Date:                By:             Rev:        Desc:                         **
** ------------------------------------------------------------------------------ **
** 29/10/2012           Jlipman         1.0         Created                       **
**                                                                                **
***********************************************************************************/
BEGIN

        -- Declare some variables to use
        DECLARE
                @SqlToExecute varchar(max),
                @TableToProcess varchar(max);


        -- Populate Cursor1 (Used to hold valid table names)
        DECLARE Cursor1 CURSOR FOR
            SELECT
                TABLE_CATALOG + '.' + TABLE_SCHEMA + '.' + TABLE_NAME
            FROM
                INFORMATION_SCHEMA.COLUMNS
            WHERE
                COLUMN_NAME = @p_Column
            ORDER BY
                TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME;


        -- Create temporary table to hold results
    CREATE TABLE #CountRecordsPerTablePerCol
                (myValue int, myTable varchar(max), myCount int);


    -- Iterate through cursor1
    OPEN Cursor1
        FETCH NEXT FROM Cursor1 INTO @TableToProcess
        WHILE @@FETCH_STATUS = 0
        BEGIN
                SET @SqlToExecute = '
                SELECT DISTINCT
                        ' + CAST(@p_Value AS VARCHAR) + ',
                        ''' + @TableToProcess + ''',
                        COUNT(*) AS Count
                FROM
                        ' + @TableToProcess + '
                WHERE
                        ' + @p_Column + ' = ''' + CAST(@p_Value AS VARCHAR) + '''
                GROUP BY
                        ' + @p_Column + '';
            INSERT #CountRecordsPerTablePerCol
                EXEC(@SqlToExecute);
            FETCH NEXT FROM Cursor1 INTO @TableToProcess
        END
        CLOSE Cursor1;
        DEALLOCATE Cursor1;


    -- Generate final query and execute it
    SELECT * FROM #CountRecordsPerTablePerCol;

END
  1.  IF OBJECT_ID('usp_CountRecordsPerTablePerColumn', 'P') IS NOT NULL 
  2.      DROP PROCEDURE [usp_CountRecordsPerTablePerColumn]
  3.  GO 
  4.   
  5.  CREATE PROCEDURE [usp_CountRecordsPerTablePerColumn] 
  6.  ( 
  7.          @p_Value int, 
  8.          @p_Column varchar(max) 
  9.  ) 
  10.  AS 
  11.  /*********************************************************************************** 
  12.  ** Author:      Joel Lipman                                                       ** 
  13.  ** Created On:  29 October 2012                                                   ** 
  14.  ** Revision:    v1.0                                                              ** 
  15.  ** Description: Stored Procedure to return counts of records per staging database ** 
  16.  **              table that contains a specific value.                             ** 
  17.  **              NOTE: This script can be run separate to a stored procedure by    ** 
  18.  **                    declaring @p_Value and @p_Column and using only the code    ** 
  19.  **                    from the BEGIN to the END (exclusive) - allows you to run   ** 
  20.  **                    this against any table only changing the parameter column.  ** 
  21.  **                                                                                ** 
  22.  ** Usage:                                                                         ** 
  23.  **    EXEC [usp_CountRecordsPerTablePerColumn] '3', 'Customer_ID';                ** 
  24.  **                                                                                ** 
  25.  ** Parameters :                                                                   ** 
  26.  **    - Value:  the value you want to match on (eg. Customer_ID)                  ** 
  27.  **    - Column: the column name which will contain this value.                    ** 
  28.  **                                                                                ** 
  29.  ** Return Values : Table of three columns:                                        ** 
  30.  **    - myValue: value specified in parameter 1 (matched in database to search)   ** 
  31.  **    - myTable: table in which the column occurs                                 ** 
  32.  **               (returned as "database.schema.tablename")                        ** 
  33.  **    - myCount: the number of records matching the value in this table.          ** 
  34.  **                                                                                ** 
  35.  ************************************************************************************ 
  36.  ** Change History:                                                                ** 
  37.  ************************************************************************************ 
  38.  ** Date:                By:             Rev:        Desc:                         ** 
  39.  ** ------------------------------------------------------------------------------ ** 
  40.  ** 29/10/2012           Jlipman         1.0         Created                       ** 
  41.  **                                                                                ** 
  42.  ***********************************************************************************/ 
  43.  BEGIN 
  44.   
  45.          -- Declare some variables to use 
  46.          DECLARE 
  47.                  @SqlToExecute varchar(max), 
  48.                  @TableToProcess varchar(max)
  49.   
  50.   
  51.          -- Populate Cursor1 (Used to hold valid table names) 
  52.          DECLARE Cursor1 CURSOR FOR 
  53.              SELECT 
  54.                  TABLE_CATALOG + '.' + TABLE_SCHEMA + '.' + TABLE_NAME 
  55.              FROM 
  56.                  INFORMATION_SCHEMA.COLUMNS 
  57.              WHERE 
  58.                  COLUMN_NAME = @p_Column 
  59.              ORDER BY 
  60.                  TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME; 
  61.   
  62.   
  63.          -- Create temporary table to hold results 
  64.      CREATE TABLE #CountRecordsPerTablePerCol 
  65.                  (myValue int, myTable varchar(max), myCount int)
  66.   
  67.   
  68.      -- Iterate through cursor1 
  69.      OPEN Cursor1 
  70.          FETCH NEXT FROM Cursor1 INTO @TableToProcess 
  71.          WHILE @@FETCH_STATUS = 0 
  72.          BEGIN 
  73.                  SET @SqlToExecute = ' 
  74.                  SELECT DISTINCT 
  75.                          + CAST(@p_Value AS VARCHAR) + ', 
  76.                          ''+ @TableToProcess + ''', 
  77.                          COUNT(*) AS Count 
  78.                  FROM 
  79.                          + @TableToProcess + ' 
  80.                  WHERE 
  81.                          + @p_Column + ' = ''+ CAST(@p_Value AS VARCHAR) + ''' 
  82.                  GROUP BY 
  83.                          + @p_Column + ''
  84.              INSERT #CountRecordsPerTablePerCol 
  85.                  EXEC(@SqlToExecute)
  86.              FETCH NEXT FROM Cursor1 INTO @TableToProcess 
  87.          END 
  88.          CLOSE Cursor1; 
  89.          DEALLOCATE Cursor1; 
  90.   
  91.   
  92.      -- Generate final query and execute it 
  93.      SELECT * FROM #CountRecordsPerTablePerCol; 
  94.   
  95.  END 

Issues
Consider adding a drop of the temporary table to the start of this script if you plan on running this successively against several databases:
copyraw
DROP TABLE #CountRecordsPerTablePerCol
  1.  DROP TABLE #CountRecordsPerTablePerCol 
or equally
copyraw
IF OBJECT_ID(N'tempdb..#CountRecordsPerTablePerCol', N'U') IS NOT NULL 
          DROP TABLE #CountRecordsPerTablePerCol;
     GO
  1.  IF OBJECT_ID(N'tempdb..#CountRecordsPerTablePerCol', N'U') IS NOT NULL 
  2.            DROP TABLE #CountRecordsPerTablePerCol; 
  3.       GO 
Category: Databases :: Article: 442

Credit where Credit is Due:


Feel free to copy, redistribute and share this information. All that we ask is that you attribute credit and possibly even a link back to this website as it really helps in our search engine rankings.

Disclaimer: The information on this website is provided without warranty and any content is merely the opinion of the author. Please try to test in development environments prior to adapting them to your production environments. The articles are written in good faith and, at the time of print, are working examples used in a commercial setting.

Thank you for visiting and, as always, we hope this website was of some use to you!

Kind Regards,

Joel Lipman
www.joellipman.com

Related Articles

Joes Revolver Map

Accreditation

Badge - Certified Zoho Creator Associate
Badge - Certified Zoho Creator Associate

Donate & Support

If you like my content, and would like to support this sharing site, feel free to donate using a method below:

Paypal:
Donate to Joel Lipman via PayPal

Bitcoin:
Donate to Joel Lipman with Bitcoin - Valid till 8 May 2022 3QnhmaBX7LQSRsC9hh6Je9rGQKEGNQNfPb
© 2021 Joel Lipman .com. All Rights Reserved.