Tag: syndicated

One of the things that is needed in quite a few functions is the object_id of a particular table/view/procedure/function in SQL Server. For example, I was looking at STATS_DATE recently, and it has this definition.

STATS_DATE (object_id, stats_id)

In the past, I’d run something like this:

DECLARE @i INT
SELECT @i = object_id
 FROM sys.objects 
 WHERE name = 'SalesOrderHeader'
SELECT STATS_DATE ( @i , 2)

Actually, I’d really do this as two batches.

SELECT * FROM sys.objects WHERE name = 'SalesOrderHeader'
SELECT STATS_DATE ( 1266103551 , 2)  

I’d run the first, get the ID, and paste it into the second. However I’ve learned that isn’t the best way to do this. In fact, when I started doing  a lot of encryption testing and research, I started to take advantage of functions like OBJECT_ID.

Now, here’s what I’d do:

SELECT STATS_DATE ( OBJECT_ID(‘Sales.SalesOrderHeader’) , 2) 

Simple, easy, and I can do this inline. With SQL Prompt, I’m also pretty quick getting this out. Of course, I do need to remember to include the schema, because this won’t work:

SELECT STATS_DATE ( OBJECT_ID(‘SalesOrderHeader’) , 2) 

Three warnings. First, qualify your objects. In this case, I should have used Sales.SalesOrderHeader to be sure I get the correct object. There are people that use schemas with the same object in multiple schemas (etl.SalesOrderHeader, audit.SalesOrderHeaders, etc.).

Second, the object_id() isn’t guaranteed to be unique across databases. I should have pointed that out.

Last, isolation can be an issue here. There’s a Connect item you can vote on to ask this be fixed: Metadata functions should follow same isolation semantics as metadata queries

SQLNewBlogger

When I find quick tricks or techniques I use often, I try to make a note and then write about them later. It helps me remember, but it also lets me share things with others.

Perhaps most important, it shows I’m doing and learning things in my career.