SELECT FROM sys tables does not work with 3 part database naming?

  • Maxer

    SSCrazy Eights

    Points: 8958

    I am running a query from a utilities database, and I want to query a vendor database we have.

    I am running the query below from inside the vendor database and it works as expected.

    SELECT * FROM
    sys.dm_db_index_usage_stats xINNER JOIN sys.indexes iON x.object_id = i.object_idAND x.index_id = i.index_id

     

    Then when I change to use the Utilities database and prefix the tables in the above query with VendorDatabase.

    I get totally different results.

     

    I'm missing something obvious here I assume?  I want to run this from a stored procedure in the utilities database.

    SELECT * FROM VendorDB.sys.dm_db_index_usage_stats x
    INNER JOIN VendorDB.sys.indexes i
    ON x.object_id = i.object_id
    AND x.index_id = i.index_id
  • Jeff Moden

    SSC Guru

    Points: 995624

    sys.dm_db_index_usage_stats covers all of the databases no matter which database you call it from.  You have to identify the database_id in your query.

     

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff Moden

    SSC Guru

    Points: 995624

    I think something like this is what you're looking for...

     SELECT * 
    FROM sys.dm_db_index_usage_stats x
    JOIN VendorDB.sys.indexes i
    ON x.object_id = i.object_id
    AND x.index_id = i.index_id
    WHERE x.database_id = DB_ID(VendorDB)
    ;

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply