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;
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?
A metadata function mentioned in a computed column in a
#temp table is going to reference
yourdatabase.sys.objects, since the table is created in
tempdb. Try adding the database prefix to the insert, and that will make
OBJECT_ID go find it in the right place:
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 ('msdb.dbo.sp_send_dbmail'); SELECT name, [object_id] FROM #procs;
dbo.sp_send_dbmail NULL msdb.dbo.sp_send_dbmail 283148054
You are doing overly overly overly complicated something trivial. Remove the computed column, insert
What happens is that you're lossing control over where is
OBJECT_ID evaluated (in which database context).
OBJECT_ID will return different values, for same name, in different DBS, or may return NULL for names that do not match current execution context (db). By adding the evaluation onto a
#temp table you can't be sure where does the evaluation occur. Is it in
msdb? In current db? Well, it looks like is not where you expected it to be, that's sure...
Ditch the fancy cleverness, stick to KISS.
Next, fix your
`JOIN #procs ON #procs.[object_id] = procstats.[object_id], as is incorrect. You need to join on