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

• Maxer

  SSCrazy Eights

  Points: 8958

  More actions

  January 16, 2020 at 10:31 pm

  Go to Answer

  #3716154

  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: 995976

  More actions

  January 16, 2020 at 10:49 pm

  #3716159

  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: 995976

  More actions

  January 16, 2020 at 10:53 pm

  Answer

  #3716160

  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)