I'm trying to produce a usage report for some stored procedures in my database. The view view sys.dm_exec_procedure_stats contains some of what I need, so I decided to start here.
SQL Server keys procedure metadata by object ID. I know only the procedure names, so first I have to map the names to IDs using the OBJECT_ID function.
There are several procedures to check, so I want to put their names in a temp table before processing them.
I thought it would be cute to get the object id from a computed column in the temp table, so I created a table like this:
USE msdb;
GO
CREATE TABLE #procs (
name SYSNAME,
[object_id] AS OBJECT_ID(name)
);
INSERT INTO #procs (name) VALUES ('dbo.sp_send_dbmail');
INSERT INTO #procs (name) VALUES ('dbo.sp_add_job');
INSERT INTO #procs (name) VALUES ('dbo.sp_start_job');
With that table in place, the obvious way to get the execution counts looked like this:
SELECT #procs.name, execution_count
FROM sys.dm_exec_procedure_stats AS procstats
INNER JOIN #procs ON #procs.[object_id] = procstats.[object_id]
ORDER BY execution_count DESC;
But it returns an empty result set!
This query shows why the result set is empty:
SELECT name, [object_id]
FROM #procs;
The object_id column is NULL, so an inner join will produce no rows:
name object_id
-------------------- -----------
dbo.sp_send_dbmail NULL
dbo.sp_add_job NULL
dbo.sp_start_job NULL
It's not that computed columns are broken for temp tables. If I add a computed column to reverse the name, it produces the correct value when I select from it:
ALTER TABLE #procs ADD reverse_name AS REVERSE(name);
SELECT name, [object_id], reverse_name
FROM #procs;
Result:
name object_id reverse_name
-------------------- ----------- ---------------------
dbo.sp_send_dbmail NULL liambd_dnes_ps.obd
dbo.sp_add_job NULL boj_dda_ps.obd
dbo.sp_start_job NULL boj_trats_ps.obd
Why does OBJECT_ID return NULL here?
Best Answer
A metadata function mentioned in a computed column in a
#temp
table is going to referencetempdb.sys.objects
, notyourdatabase.sys.objects
, since the table is created intempdb
. Try adding the database prefix to the insert, and that will makeOBJECT_ID
go find it in the right place:Results: