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)

Answer 2

Well it depends on what do you consider as Pure sql I've come up with the following solution. It is purely written in T-SQL but uses dynamically built query

-- Using variables just for better readability.
DECLARE @Name NVARCHAR(4000)
DECLARE @Schema NVARCHAR(4000)
DECLARE @Query NVARCHAR(4000)

-- Get the relevant data
SET @Schema = QUOTENAME(OBJECT_SCHEMA_NAME(613577224))
SET @Name = QUOTENAME(OBJECT_NAME(613577224))
-- Build query taking into consideration the schema and possible poor object naming
SET @Query = 'SELECT COUNT(*) FROM ' + @Schema + '.' + @Name + ''
-- execute it.
EXEC(@Query)

EDIT

The changes consider the possible faulty cases described in the comments.

I've outlined the variables, because this is a convenient approach for me. Cheers.