BaseLineTableRowsCount_Proc.sql

--DROP PROCEDURE BaseLineTableRowsCount_Proc
CREATE PROCEDURE BaseLineTableRowsCount_Proc
AS
BEGIN
	SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
	IF ( NOT EXISTS (SELECT * 
					 FROM INFORMATION_SCHEMA.TABLES
					 WHERE TABLE_SCHEMA = 'dbo' 
					 AND  TABLE_NAME = 'BaseLineTableRowsCount'))
	BEGIN
		PRINT 'Table dbo.[BaseLineTableRowsCount] is not exist'
		CREATE TABLE dbo.BaseLineTableRowsCount(
			proc_id 				int,
			start_time				datetime,				
			table_name				varchar(64),
			rows_count				bigint,
			rows_delta				bigint default(0),
			rows_delta_delta		bigint default(0)
		)
		PRINT 'Created table dbo.[BaseLineTableRowsCount]'
	END

	IF ( NOT EXISTS (SELECT * 
					 FROM INFORMATION_SCHEMA.TABLES
					 WHERE TABLE_SCHEMA = 'dbo' 
					 AND  TABLE_NAME = 'BaseLineTableRowsCountIgnore'))
	BEGIN
		PRINT 'Table dbo.[BaseLineTableRowsCountIgnore] is not exist'
		CREATE TABLE dbo.BaseLineTableRowsCountIgnore(			
			table_name				varchar(64)
		)

		INSERT INTO dbo.[BaseLineTableRowsCountIgnore](table_name)
		VALUES ('BaseLineTableRowsCount'), 
			   ('BaseLineTableRowsCountIgnore')

		PRINT 'Created table dbo.[BaseLineTableRowsCountIgnore]'
	END	

	-- Порядковый номер процедуры
	DECLARE @currentProcID INT 
	-- Выбираем последний номер, и просто добавляем единичку
	SELECT @currentProcID = ISNULL(MAX(proc_id), 0) + 1 FROM dbo.[BaseLineTableRowsCount]
	PRINT 'Current procedure id is: ' + CAST(@currentProcID as varchar)

	DECLARE @currentDate DATETIME
	SELECT @currentDate = CURRENT_TIMESTAMP
	
	PRINT 'Requesting rows count for tables...'
	INSERT INTO dbo.[BaseLineTableRowsCount] (proc_id, start_time, table_name, rows_count) 
	SELECT @currentProcID, @currentDate, tbl.name, CAST(p.rows AS bigint)
		FROM sys.tables AS tbl
		INNER JOIN sys.indexes AS idx ON idx.object_id = tbl.object_id and idx.index_id < 2
		INNER JOIN sys.partitions AS p ON p.object_id=CAST(tbl.object_id AS int)
		AND p.index_id=idx.index_id
		WHERE SCHEMA_NAME(tbl.schema_id)='dbo'
			  AND tbl.name NOT IN ( SELECT table_name from dbo.[BaseLineTableRowsCountIgnore])
	
	IF(@currentProcID > 1)
	BEGIN
		PRINT 'Calculating deltas...'
		UPDATE t1
		SET t1.rows_delta = t1.rows_count - t2.rows_count,
			t1.rows_delta_delta = t1.rows_count - t2.rows_count - t2.rows_delta
		FROM dbo.[BaseLineTableRowsCount] AS t1
		INNER JOIN dbo.[BaseLineTableRowsCount] AS t2
		ON t1.table_name = t2.table_name
		WHERE t1.proc_id = @currentProcID 
		AND t2.proc_id = @currentProcID - 1;
	END

	--select * from BaseLineTableRowsCount where rows_delta != 0 order by proc_id desc
	--drop table BaseLineTableRowsCount
END

-- exec BaseLineTableRowsCount_Proc