December 18, 2014 at 5:48 am
I run the script below once a day to keep track of row count over time. I would like to compare the results from today and yesterday
to see if anyone deleted more than 20% of data from any given table. How would I do this? I really don't need the data anymore than
a day just to compare the results.
Mon - Run script to collect row count
Tues - Run script to collect current row into temp table
,compare all row count in both tables
,purge records from Monday and insert current
Wed - Run script to collect current row into temp table
,compare all row count in both tables
,purge records from Tuesday and insert current
Use MyDB
Go
INSERT INTO Utility.dbo.MyDB_RowCnt
Select QUOTENAME(SCHEMA_NAME(sOBJ.schema_id)) + '.' + QUOTENAME(sOBJ.name) AS [TableName]
, SUM(sPTN.Rows) AS [RowCount], GetDate() as Date
FROM
sys.objects AS sOBJ
INNER JOIN sys.partitions AS sPTN
ON sOBJ.object_id = sPTN.object_id
WHERE
sOBJ.type = 'U'
AND sOBJ.is_ms_shipped = 0x0
AND index_id < 2 -- 0:Heap, 1:Clustered
GROUP BY
sOBJ.schema_id
, sOBJ.name
ORDER BY [TableName]
GO
December 18, 2014 at 6:38 am
Look up the LAG or LEAD keywords in Books Online.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
December 18, 2014 at 6:57 am
--update dbo.MyDB_RowCnt
begin tran tt;
delete from Utility.dbo.MyDB_RowCnt
where flag ='OLD';
update Utility.dbo.MyDB_RowCnt
set flag ='OLD'
where flag ='NEW';
INSERT INTO Utility.dbo.MyDB_RowCnt
Select 'NEW' as flag
,QUOTENAME(SCHEMA_NAME(sOBJ.schema_id)) + '.' + QUOTENAME(sOBJ.name) AS [TableName]
, SUM(sPTN.Rows) AS [RowCount], GetDate() as Date
FROM ...
commit tran tt;
-- analyze OLD against NEW
P.S. If number of tables can possibly vary i'd better opt to FULL JOIN instead of LEAD /LAG.
December 18, 2014 at 7:30 am
P.S. If number of tables can possibly vary i'd better opt to FULL JOIN instead of LEAD /LAG.
I don't think FULL JOIN would be required. LEAD/LAG should still work because they will return NULL values (which can be defaulted to 0 to make the math work) for non-matching offset-rows.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
December 18, 2014 at 4:20 pm
1) Cluster the table on ( Date, TableName )
2) Edit: The variables for date are just in case you later want to keep more history and do other rowcount comparisons. For example, you could compare rowcounts one month apart, 1 year apart, etc..
DECLARE @first_date_to_compare datetime
DECLARE @second_date_to_compare datetime
SELECT @first_date_to_compare = (SELECT TOP (1) Date FROM dbo.MyDB_RowCnt ORDER BY Date DESC)
SELECT @second_date_to_compare = (SELECT TOP (1) Date FROM dbo.MyDB_RowCnt WHERE Date < @first_date_to_compare ORDER BY Date DESC)
SELECT
rc.TableName,
MAX(CASE WHEN rc.Date = @first_date_to_compare THEN rc.[rowcount] END) AS Current_Rowcount,
MAX(CASE WHEN rc.Date = @second_date_to_compare THEN rc.[rowcount] END) AS Previous_rowcount
FROM dbo.MyDB_RowCnt rc
WHERE
rc.Date IN (@first_date_to_compare, @second_date_to_compare)
GROUP BY rc.TableName
HAVING
MAX(CASE WHEN rc.Date = @first_date_to_compare THEN rc.[rowcount] END) <
MAX(CASE WHEN rc.Date = @second_date_to_compare THEN rc.[rowcount] END) * 0.8
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply