How do I compare all tables with nonzero rowcounts (in an archive db) with tables of the same name in the line of business db?
;with t1 as (
SELECT sc.NAME + '.' + ta.NAME tablename,
   Sum(pa.rows)            rowcnt
FROM   ARC_DB.sys.tables ta
   INNER JOIN ARC_DB.sys.partitions pa
           ON pa.object_id = ta.object_id
   INNER JOIN ARC_DB.sys.schemas sc
           ON ta.schema_id = sc.schema_id
WHERE  ta.is_ms_shipped = 0
   AND pa.index_id IN ( 1, 0 )
   and pa.rows &gt; 0
GROUP  BY sc.NAME,
      ta.NAME
)
select t1.tablename, t1.rowcnt, t2.rowcnt, t1.rowcnt-t2.rowcnt as diff
from t1
join (
SELECT sc.NAME + '.' + ta.NAME tablename,
   Sum(pa.rows)            rowcnt
FROM   LOB_DB.sys.tables ta
   INNER JOIN LOB_DB.sys.partitions pa
           ON pa.object_id = ta.object_id
   INNER JOIN LOB_DB.sys.schemas sc
           ON ta.schema_id = sc.schema_id
WHERE  ta.is_ms_shipped = 0
   AND pa.index_id IN ( 1, 0 )
GROUP  BY sc.NAME,
      ta.NAME
) as t2 on t2.tablename = t1.tablename