count rows in each table and compare

in an article in MSSQLTips Dattatrey Sindol wrote an article entitled “SQL Server Row Count for all Tables in a Database”  with several ways to get the list of tables. One used sys.partitions catalog, another sys.dm_partition_stats DMV, the third sp_MSForeachtable sp and the last a coalesce function

I used the first one (the first that i came to). It does not reference the database name – you have to select the database when you open the query window or put a “using dbName” statement in it. This means you can’t put that code into a USP.

We are going to get periodic updates of a database. If we store a copy of the older database, we can used somethng like this to count and list the tables in the older and newer databases, along with list of their row counts. This will tell us if a table is dropped in the new database, or has been added, and if the row counts are reasonable – typically increasing, if a reference table, it might remain the same, and if there is an error, the counts might fall. Or if the tabale is a log or audit table which was cleared during maintenance before the time you run the code.

Here is Datta’s code

SELECT
 QUOTENAME(SCHEMA_NAME(sOBJ.schema_id)) + '.' + QUOTENAME(sOBJ.name) AS [TableName] , SUM(sPTN.Rows) AS [RowCount]
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

What i do is to put the results of each database into a table, union the 2 tables then order by table name. I put both table name into a coalesce function, both row counts and the difference into the data from newTable while left joining to old table, and union that to newtable RIGHT join to the old table. This gets me both new tables (in newTable, not in oldTable) and depreciated tables (in old not in new). This is seen as both a null in oldName or newName and a null in the rowcount.

select coalesce(newTable.tableName, oldTable.tableName) as 
TableName
, newTable.[rowcount] as newCount
, oldTable.[rowcount] as oldCount
, (newTable.[rowcount])-(oldTable.[rowcount]) as difference
from [Ntier_MIPU].dbo.newTable 
left join [Ntier_MIPU_2014].dbo.oldTable 
on newTable.tableName = oldTable.TableName
UNION
select coalesce(newTable.tableName, oldTable.tableName) as 
TableName
, newTable.[rowcount] as newCount
, oldTable.[rowcount] as oldCount
, (newTable.[rowcount])-(oldTable.[rowcount]) as difference
from [Ntier_MIPU].dbo.newTable 
right join [Ntier_MIPU_2014].dbo.oldTable 
on oldTable.TableName= newTable.tableName
    where oldtable.TableName != '[dbo].[oldTable]' 
and where newtable.TableName != '[dbo].[newTable]'
order by tableName
Advertisements