Introduction: You setup your analytical server (OLAP) with isolation from your transaction server (OLTP) to follow best practices. You also setup an ongoing replication in near real-time. Business users are happy as they can analyze all the historical data (great for analysis that requires volume of data) as well as see real-time data (great for daily and hourly performance). But how do keep track that all your dependent tables are in-sync?
Problem
How do you compare data between source and destination tables using a repeatable process? One of the first reporting needs in a Data Governance report is comparing counts to identify any missing records. Having a code that allows to get a record count from any table anywhere (local/remote server) will make it easy.
Solution
Daily Data Governance Report that captures record counts between source and destination tables and tracks trending and leverages a reusable stored procedure.
data-governance

The biggest challenge is to use the same functionality to capture the record count and to avoid using COUNT(*) which in most cases would cause performance issues. The following stored procedure does just that – captures records anywhere and using statistical record count from sys.dm_db_partition_stats to improve performance.

USE [yourdatabasename]
GO
/****** Object: StoredProcedure [dbo].[usp_GET_RecordCountfromAnyTable] Script Date: 4/23/2019 3:41:40 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Steve Rezhener
-- Create date: 04/23/2019
-- Description: Getting record count from any table anywhere (local/remote server)
-- EXEC dbo.usp_GET_RecordCountfromAnyTable @TagName = 'AWS EC2', @ServerName= NULL, @DatabaseName='yourdatabase', @SchemaAndTableName='yourtablename'

— =============================================
CREATE PROCEDURE [dbo].[usp_GET_RecordCountfromAnyTable]
@TagName AS VARCHAR(20)
,@ServerName AS VARCHAR(100)
,@DatabaseName AS VARCHAR(50)
,@SchemaAndTableName AS VARCHAR(100)

AS
BEGIN
— SET NOCOUNT ON added to prevent extra result sets from
— interfering with SELECT statements.
SET NOCOUNT ON;

DECLARE @SQLString1 AS NVARCHAR(MAX)
DECLARE @SQLString2 AS NVARCHAR(MAX)
DECLARE @ObjectID AS BIGINT
DECLARE @ParmDefinition nvarchar(500);
DECLARE @SchemaName AS VARCHAR(50) = PARSENAME(@SchemaAndTableName,2)
DECLARE @TableName AS VARCHAR(50) = PARSENAME(@SchemaAndTableName,1)

IF ISNULL(@ServerName,@@SERVERNAME) = @@SERVERNAME — local
BEGIN

IF @ServerName IS NULL SET @ServerName = @@SERVERNAME

SET @SQLString1 = ‘SELECT @ObjectIDOUT = tabs.OBJECT_ID
FROM ‘ + @DatabaseName + ‘.sys.tables AS tabs
INNER JOIN ‘ + @DatabaseName + ‘.sys.schemas AS mas ON tabs.schema_id = mas.schema_id
WHERE 1=1
AND tabs.name = ”’ + @TableName + ”’
AND mas.name = ”’ + @SchemaName + ”’

PRINT ‘@SQLString1: ‘ + @SQLString1
SET @ParmDefinition = N’@ObjectIDOUT BIGINT OUTPUT’;
EXECUTE sp_executesql @SQLString1, @ParmDefinition, @ObjectIDOUT = @ObjectID OUTPUT
PRINT ‘@ObjectID: ‘ + CONVERT(VARCHAR,@ObjectID)

SET @SQLString2 = N’SELECT ”’ + @TagName + ”’ AS TagName, ”’ + @ServerName + ”’ AS ServerName, ”’ + @DatabaseName + ”’ AS DatabaseName, ”’ + @SchemaAndTableName + ”’ AS SchemaAndTableName, row_count AS RecCount
FROM ‘ + @DatabaseName + ‘.sys.dm_db_partition_stats
WHERE 1=1 AND object_id = ‘ + CONVERT(VARCHAR,@ObjectID) + ‘ AND index_id=1’

PRINT ‘@SQLString2: ‘ + @SQLString2
EXECUTE sp_executesql @SQLString2

END
ELSE — remote server via linked server
BEGIN

SET @SQLString1 = ‘SELECT @ObjectIDOUT = tabs.OBJECT_ID
FROM [‘ + @ServerName + ‘].’ + @DatabaseName + ‘.sys.tables AS tabs
INNER JOIN [‘ + @ServerName + ‘].’ + @DatabaseName + ‘.sys.schemas AS mas ON tabs.schema_id = mas.schema_id
WHERE 1=1
AND tabs.name = ”’ + @TableName + ”’
AND mas.name = ”’ + @SchemaName + ”’

PRINT ‘@SQLString1: ‘ + @SQLString1
SET @ParmDefinition = N’@ObjectIDOUT BIGINT OUTPUT’;
EXECUTE sp_executesql @SQLString1, @ParmDefinition, @ObjectIDOUT = @ObjectID OUTPUT
PRINT ‘@ObjectID’ + CONVERT(VARCHAR,@ObjectID)

SET @SQLString2 = N’SELECT ”’ + @TagName + ”’ AS TagName, ”’ + @ServerName + ”’ AS ServerName, ”’ + @DatabaseName + ”’ AS DatabaseName, ”’ + @SchemaAndTableName + ”’ AS SchemaAndTableName, row_count AS RecCount
FROM [‘ + @ServerName + ‘].’ + @DatabaseName + ‘.sys.dm_db_partition_stats
WHERE 1=1 AND object_id = ‘ + CONVERT(VARCHAR,@ObjectID) + ‘ AND index_id=1’
–PRINT ‘@SQLString2: ‘ + @SQLString2

PRINT ‘@SQLString2: ‘ + @SQLString2
EXECUTE sp_executesql @SQLString2

END

END