MSSQL: given a table's object_id, determine whether it is empty

Question

For a bit of database-sanity checking code, I'd like to determine whether a particular object_id corresponds to an empty table.

Is there some way to (for instance) select count(*) from magic_operator(my_object_id) or similar?

I'd strongly prefer a pure-sql solution that can run on MS SQL server 2008b.

Answer 1

You can get a rough idea from

SELECT SUM(rows)
FROM sys.partitions p 
WHERE index_id < 2 and p.object_id=@my_object_id

If you want guaranteed accuracy you would need to construct and execute a dynamic SQL string containing the two part object name. Example below though depending on how you are using this you may prefer to use sp_executesql and return the result as an output parameter instead.

DECLARE @DynSQL nvarchar(max) = 
            N'SELECT CASE WHEN EXISTS(SELECT * FROM ' + 
            QUOTENAME(OBJECT_SCHEMA_NAME(@my_object_id)) + '.' + 
                   QUOTENAME(OBJECT_NAME(@my_object_id)) +
           ') THEN 0 ELSE 1 END AS IsEmpty'


EXECUTE (@DynSQL)