MSSQL: given the table object_id, determine if it is empty

Question

For a little database health check code, I would like to determine if a particular object_id matches an empty table.

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

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

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 need guaranteed accuracy, you will need to build and execute a dynamic SQL string containing the name of the object with two parts. The example is below, although depending on how you use it, you can use sp_executesql and instead return the result as an output parameter.

 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 you consider Pure sql I came up with the following solution. It is written exclusively in T-SQL, but uses a 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 address possible erroneous cases described in the comments.

I set out the variables because this is a convenient approach for me. Greetings.