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

Maybe just grab the first row, and display a 1?

select top 1 1 from tablename

Answer 2

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 3

SELECT TOP 1 ID FROM TABLE

Then you can do an EOF check when the recordset is returned.

Answer 4

sp_spaceused will probably be more efficient than COUNT(*).

Keep in mind that it does not update in real time so it may not be 100% accurate in all cases.

Answer 5

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

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