Index physical stats DMV - Object ID/table not found

Question

I have installed SQL Server 2012 RC0 on my laptop recently and I was trying a demo which illustrates how shrink database will cause fragmentation. I created a table, a clustered index on the identity column and inserted a few records and then issued the below to check fragmentation:

select avg_fragmentation_in_percent from sys.dm_db_index_physical_stats(DB_ID('databasename'), 
  OBJECT_ID('tablename'), 1, NULL, 'limited')

This is the error message I get:

Could not find table or object ID 2139154666. Check system catalog.

I did a select * from sysobjects and the Object exists with that objectID. Why does SQL Server think that it doesn't exist? What am I doing wrong?

Answer 1

You are in the wrong database context.

Try:

select avg_fragmentation_in_percent 
from databasename.sys.dm_db_index_physical_stats(DB_ID('databasename'), OBJECT_ID('tablename'), 1, NULL, 'limited')

Alternatively you can just throw a

USE Databasename

on the first line.

I'm guessing that it's executing in the context of master or something similar, which won't have the OBJECTID you are looking for.