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
- 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
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: or equally
copyraw
IF OBJECT_ID(N'tempdb..#CountRecordsPerTablePerCol', N'U') IS NOT NULL
DROP TABLE #CountRecordsPerTablePerCol;
GO
- IF OBJECT_ID(N'tempdb..#CountRecordsPerTablePerCol', N'U') IS NOT NULL
- DROP TABLE #CountRecordsPerTablePerCol;
- GO
Category: Databases :: Article: 442
