Dirty Tricks - Comparing rowcount

Content Rating: Beginner

I don't have many cool tools at my current gig to help me out, so I'm often forced to come up with quick and dirty solutions of my own. Today I have to compare row counts in two identical databases, one in Dev and one in QA. Here's what I did.

1. Generate a list of select count(*) statements

— Get the SELECT statements for rowcounts for all user tables

select 'select ''' + name + ''' nm, count(*) cnt from ' + [name] + ' UNION '

from sys.objects where type_desc = 'USER_TABLE' order by [name]

This generates a list of statements like this:

select 'Client' nm, count(*) cnt from Client UNION

2. Copy-paste that list into a query window and run. (Note that we remove the last Union statement).

select 'Client' nm, count(*) cnt from Client UNION

select 'Supplier' nm, count(*) cnt from Client UNION

select 'Anchor' nm, count(*) cnt from Client

This gets us a resultset like this:

nm cnt

Client 12333

Supplier 401

Anchor 32

3. Do the same on the other environment.

Now at this point, you have your data. If there are few enough tables, you can compare by hand. Or you could run each set of SELECT statements with Save As File, import those file to your sandbox DB as two tables, and compare rowcounts that way.

Edit: That's the old school way of getting rowcounts from pre-DMV days. So if you're using SQL Server 2000 or earlier, or need exact rowcounts, use the above. If you'd like something faster and easier, check out Aaron Bertrand's (blog | twitter) solutions in the comments below!

Another day, another dirty little trick from the MidnightDBA. Show us your dirty tricks, or a better way to compare – either blog or post in comments!

-Jen McCown
http://www.MidnightDBA.com