CREATE PROC CompareRowsBetweenDatabas AS WITH A AS( SELECT sch.name AS SchemaName, st.Name AS TableName, SUM( CASE WHEN (p.index_id < 2) AND (a.type = 1) THEN p.rows ELSE 0 END ) AS Rows FROM sys.partitions p INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id INNER JOIN sys.tables st ON st.object_id = p.Object_ID INNER JOIN sys.schemas sch ON sch.schema_id = st.schema_id GROUP BY st.name, sch.name ), b as( SELECT sch.name AS SchemaName, st.Name AS TableName, SUM( CASE WHEN (p.index_id < 2) AND (a.type = 1) THEN p.rows ELSE 0 END ) AS Rows FROM B.sys.partitions p INNER JOIN B.sys.allocation_units a ON p.partition_id = a.container_id INNER JOIN B.sys.tables st ON st.object_id = p.Object_ID INNER JOIN B.sys.schemas sch ON sch.schema_id = st.schema_id GROUP BY st.name, sch.name ) SELECT a.SchemaName, a.TableName, a.Rows, b.SchemaName As BSchemaName, b.TableName AS BTableName, B.Rows AS BRows FROM a INNER JOIN b ON a.TableName = b.TableName AND a.SchemaName = b.SchemaName EXEC CompareRowsBetweenDatabase
This will give me results as I need.
Let me know if it helps you.
Microsoft Certified Professional Expert. I have 14+ years of experience in Design and Develop 100+ .Net applications using Asp.Net, C#, Asp.Net Core, VB.Net, SQL Server, MVC, AngularJS, JavaScript, Azure, HTML5 and CSS3.
Posted by Tejas Shah on November 22, 2017
Posted by Tejas Shah on November 11, 2017
Posted by Tejas Shah on August 12, 2015
Posted by Tejas Shah on June 26, 2015
Posted by Tejas Shah on April 20, 2015