Computed OBJECT_NAME column

I was writing a script to pull a bunch of meta data to dynamically generate some other scripts, and I built a temp table to house various tables and different dynamic sql strings I was planning to use. For whatever reason, I opted to make the PK the object_id and build a computed column to output the name. When I did this, I got an odd result though. Assuming you have AdventureWorks2012 installed (it doesn't need to be that version, you just need some table objects), run the following code:
use adventureworks2012
go

declare @tables table
(
    TableId int primary key clustered,
    TableName as object_name(TableID)
)

insert into @Tables (TableId)
select top 10 object_id
from sys.tables

select TableId, ComputedTableName = TableName, OnTheFlyTableName = object_name(TableId)
from @Tables
You should see something that looks like this: The problem is that when you declare the computed column on @tables, it assumes tempdb as the context for the object_name() function call. So even if you're currently using AdventureWorks2012, that object_name call is always made against tempdb. To fix the issue, you can add a second optional parameter to the object_name() function call with the database you want it to search against.
declare @tables table
(
    TableId int primary key clustered,
    TableName as object_name(TableID, db_id('AdventureWorks2012'))
)

Comments

Popular posts from this blog

Primary Key columns in INCLUDE list

65335 Factorial