What's the cheapest query I can run to see if there are any rows in the table?

Question

I've been using the sp_MSforeachtable built-in stored procedure to determine the row count of each table in our database, using COUNT(*).

I've realized, though, that I just want a 0 or 1, depending on whether there are any rows at all in the table.

Is there something else I can use that's faster/cheaper than COUNT(*)?

Answer 1

Consider this query. EXISTS will stop execution when it finds the first match.

IF EXISTS (SELECT 1 FROM MyTable)
BEGIN
   print 'at least one!'
END
ELSE
BEGIN
   print 'no rows found in table'
END

Answer 2

This will print all the table names that have at least 1 row

exec sp_MSforeachtable 'if  exists (select 1 from ?) print ''?'''