/****** Object: StoredProcedure [dbo].[CALCULATEROWS] Script Date: 22.01.2019 15:49:46 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[CALCULATEROWS]
AS
IF NOT EXISTS(SELECT TOP 1 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE' AND TABLE_NAME = '_ROWCOUNTS')
BEGIN
CREATE TABLE _ROWCOUNTS(
TABLENAME varchar(255) NULL,
OLDROWCOUNT int NULL,
NEWROWCOUNT int NULL
)
END
CREATE TABLE #COUNTS
(
TABLENAME varchar(255),
ROWCOUNT_ int
)
EXEC sp_MSForEachTable @command1='INSERT #COUNTS (TABLENAME, ROWCOUNT_) SELECT ''?'', COUNT(*) FROM ?'
DECLARE @CRSR CURSOR;
DECLARE @TN NVARCHAR(250);
BEGIN
SET @CRSR = CURSOR FOR
SELECT TABLENAME FROM #COUNTS
OPEN @CRSR
FETCH NEXT FROM @CRSR
INTO @TN
WHILE @@FETCH_STATUS = 0
BEGIN
IF NOT EXISTS (SELECT TOP 1 1 FROM _ROWCOUNTS WHERE TABLENAME = @TN)
BEGIN
INSERT INTO _ROWCOUNTS(TABLENAME,OLDROWCOUNT,NEWROWCOUNT)
VALUES(@TN,(SELECT TOP 1 ROWCOUNT_ FROM #COUNTS WHERE TABLENAME = @TN),(SELECT TOP 1 ROWCOUNT_ FROM #COUNTS WHERE TABLENAME = @TN))
END
ELSE
BEGIN
UPDATE _ROWCOUNTS SET OLDROWCOUNT = NEWROWCOUNT WHERE TABLENAME = @TN
UPDATE _ROWCOUNTS SET NEWROWCOUNT = (SELECT TOP 1 ROWCOUNT_ FROM #counts WHERE TABLENAME = @TN) WHERE TABLENAME = @TN
END
FETCH NEXT FROM @CRSR
INTO @TN
END;
CLOSE @CRSR ;
DEALLOCATE @CRSR;
END;
DROP TABLE #COUNTS
SELECT *, (NEWROWCOUNT-OLDROWCOUNT) AS DIFFERENCE_ FROM _ROWCOUNTS WHERE (NEWROWCOUNT-OLDROWCOUNT) > 0 ORDER BY DIFFERENCE_ DESC
GO
