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