1

(Full Disclosure: I am a software tester who is out of my depth. Because I am unable to get a definitive answer from the developer due to the necessary independence of the test team, I have to figure this out myself.)

This query gives me a rowcount of all the tables in the archive db:

query0:

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 )
GROUP  BY sc.NAME,
      ta.NAME
ORDER  BY Sum(pa.rows) DESC

query1:

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
ORDER  BY Sum(pa.rows) DESC

But how can I use this to compare the ARC_DB table names and row counts to the line of business (LOB_DB) db, whose tables have much larger rowcounts? Note: I do not care about those LOB_DB tables whose ARC_DB counterparts have no rows. Could I do something like giving query0 a variable in the "from" clause, giving query1 a variable through an inner join, then comparing one variable to another? Does that make sense?

Any help is super appreciated. I am on a deadline, and will respond immediately.

2
;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 > 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
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.