General Queries

DB Row Comparitor

USE [DBName] GO /** Object: StoredProcedure [dbo].[TableName.Availablitiy7Day] Script Date: 09/06/2021 15:28:51 **/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO

-- ============================================= -- Author: -- Create date: -- Description: -- ============================================= ALTER PROC [dbo].[TableName.Availablitiy7Day] AS

DECLARE @NAME VARCHAR(100) DECLARE @SQL NVARCHAR(MAX) DECLARE @ParmDefinition nvarchar(500); DECLARE @s1 nvarchar(1000); DECLARE @s2 nvarchar(1000); DECLARE @EndDate datetime; DECLARE @StartDate datetime; DECLARE @SiteName varchar(50);

SET @SiteName = 'TableName' SET @EndDate = Convert(DateTime, DATEDIFF(DAY, 0, GETDATE())) -- today at midnight SET @StartDate = Convert(DateTime, DATEDIFF(DAY, 7, GETDATE())) -- 7 days ago at midnight --this time period is the last 7 days up to midnight last night

CREATE TABLE #TableNameCOMPARISONTABLE( Id INT IDENTITY , RunDate datetime , SiteName VARCHAR(50) , TableName VARCHAR(100) , LocalRowCnt INT , RemoteRowCnt INT , RowDiff INT ) SET @ParmDefinition = N'@s2 nvarchar(1000) OUTPUT,@s1 nvarchar(1000) OUTPUT'; DECLARE CUR CURSOR FOR SELECT NAME FROM SYS.TABLES WHERE schema_id = 16 --TableName

-- SELECT SCHEMA_ID('TableName') AS Result -- THIS QUERY WILL GIVE SCHEMA_ID FROM THE SCHEMA NAME

OPEN CUR FETCH NEXT FROM CUR INTO @NAME WHILE @@FETCH_STATUS = 0 BEGIN SET @s1=NULL SET @s2=NULL SET @sql = 'SELECT @s1=CAST(COUNT(*) AS NVARCHAR(1000)),@s2='''+@NAME+ ''' FROM [TableName].'+@NAME +' where TimestampUTCsystem between '''+convert(varchar(23),@StartDate,121)+''' AND '''+convert(varchar(23),@EndDate,121)+''' ' EXECUTE sp_executesql @sql, @ParmDefinition, @S1 = @s1 OUTPUT,@S2 = @s2 OUTPUT;

INSERT INTO #TableNameCOMPARISONTABLE (RunDate, SiteName,TableName,LocalRowCnt) VALUES (getdate(),@SiteName,@s2,@s1) SET @s1=NULL SET @s2=NULL BEGIN TRY
SET @sql = 'SELECT @s1=CAST(COUNT() AS NVARCHAR(1000)),@s2='''+@NAME+ ''' FROM OPENQUERY([TableName], ''SELECT FROM '+@NAME + ' where TimestampUTCsystem between '''''+convert(varchar(23),@StartDate,121)+''''' AND '''''+convert(varchar(23),@EndDate,121)+''''' '') ' EXECUTE sp_executesql @sql, @ParmDefinition, @S1 = @s1 OUTPUT,@S2 = @s2 OUTPUT; UPDATE #TableNameCOMPARISONTABLE SET RemoteRowCnt=@s1 WHERE TableName=@s2 UPDATE #TableNameCOMPARISONTABLE SET RowDiff=(RemoteRowCnt- LocalRowCnt) WHERE TableName=@s2 END TRY BEGIN CATCH -- Access error reading from remote table DELETE FROM #TableNameCOMPARISONTABLE WHERE TableName=@NAME END CATCH; FETCH NEXT FROM CUR INTO @NAME

END CLOSE CUR DEALLOCATE CUR SELECT * FROM #TableNameCOMPARISONTABLE DROP TABLE #TableNameCOMPARISONTABLE