Technical Article

Count rows in all tables over time SQL 2K only

,

This script will record all the rows in all tables of the database it's run on.  Then, at each execution, it will compare the rows to what they originally were and display those that are different (inserts or deletes):

/*-----------------------------------------------------------------------------------------------

This script tracks the rows in a database over time.
The base table used is #TempRowCount, which has new records added everytime the script is run.
A dynamic query based on that table will display all tables that have had their row count changed since the last
time the script was run.

*/-----------------------------------------------------------------------------------------------
if object_ID('tempdb..#TempRowCount') is null
begin
create table #TempRowCount (TableName varchar(255), RowCnt int, BatchID int, Instance datetime)
end


declare @BatchID int,
@stQuery varchar(8000)


declare @TempRowCount Table (TableName varchar(255), RowCnt int, BatchID int, Instance datetime)


/*--------------------------------------------

Update Base Table with rowcount figures


*/--------------------------------------------
select top 1 
@BatchID = BatchID + 1 
from #TempRowCount (nolock) 
order by BatchID desc

IF @BatchID is null set@BatchID = 1


--Using temp table to store date before inserting into permanent table (query was hanging due to...feedback?)
Insert@TempRowCount
Select so.name, convert(int, sc.rowcnt) as RowsInTable, @BatchID, getdate()
Fromsysobjects so (nolock)
JOINsysindexes sc (nolock) on so.id = sc.id
WHERE sc.indid < 2 and so.Name <> '#TempRowCount'


--Update permanent table 
Insert#TempRowCount
Select * from @TempRowCount



/*--------------------------------------------

Create Query to turn batches into columns (the name of the column is the time it ran)

*/--------------------------------------------


declare @Query Table (BatchId int, instance datetime)
Insert @Query
Selectdistinct batchid, instance
From#TempRowCount (nolock)


--begin Select clause
select@stQuery = 'Select t.TableName, t.RowCnt ',
@BatchID = 0


--Finish Select clause
While 1 = 1
Begin

select@BatchID = BatchID,
@stQuery = @stQuery + ', (t' + cast(BatchID as varchar) + '.RowCnt - t.RowCnt) [' + convert(varchar, instance, 108) + ']'
From@Query
WhereBatchID > @BatchID
order by batchid 

If @@rowcount = 0 Break


end

--begin from clause
select@stQuery = @stQuery + ' From #TempRowCount t ',
@BatchID = 0



--Finish from clause
While 1 = 1
Begin

select@BatchID = BatchID,
@stQuery = @stQuery + ' LEFT JOIN #TempRowCount ' + 't' + cast(BatchID as varchar) + 
' (nolock) on (t.TableName = t' + cast(BatchID as varchar) + '.TableName and t' + cast(BatchID as varchar) + '.Batchid = ' + cast(BatchID as varchar) + ')'
From@Query
WhereBatchID > @BatchID
order by batchid 

If @@rowcount = 0 Break

end


--Add where clause
set @stQuery = @stQuery + ' Where t.BatchID = 1 and (t' + cast(@BatchID as varchar) + '.RowCnt - t.RowCnt) <> 0'


exec (@stQuery)

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating