Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
1  
you are doing it incorrectly. From BOL (technet.microsoft.com/en-us/library/ms186301.aspx) By default, the SQL Server Database Engine assumes that object_id is in the context of the current database. A query that references an object_id in another database returns NULL or incorrect results. –  Kin Oct 10 '13 at 14:18
1  
@Kin that's true, but since he issues a USE command, it might not be altogether intuitive from the docs why the "current database context" is not msdb. –  Aaron Bertrand Oct 10 '13 at 14:23

2 Answers 2

up vote 3 down vote accepted

A metadata function mentioned in a computed column in a #temp table is going to reference tempdb.sys.objects, not 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;

Results:

dbo.sp_send_dbmail          NULL
msdb.dbo.sp_send_dbmail     283148054
share|improve this answer
    
Thanks, this solved my problem. but I think in the end I would avoid using computed columns entirely. See my comment on Remus' answer. –  Iain Elder Nov 5 '13 at 18:32
    
@IainElder whether you use a computed column or not doesn't matter, if you're not in msdb when you insert the value, if you try to derive object_ID() at that time, it will produce NULL for the same reasons. Another important thing to note is that, for user objects, if you store the object_id(), it could change (drop/create). –  Aaron Bertrand Nov 5 '13 at 18:40

You are doing overly overly overly complicated something trivial. Remove the computed column, insert OBJECT_ID explicitly.

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 tempdb? 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 object_id and database_id.

share|improve this answer
1  
I've gone right off computed columns. In my experience, they seem to encourage confusing and unmaintainable code. Next time, I would just insert the values directly, as you say. –  Iain Elder Nov 5 '13 at 18:31

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.