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

Question

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

Maxer

SSCrazy Eights

Points: 8964
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: 1004401
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.
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 2 (of 2 total)

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

Jeff Moden SSC Guru Points: 1004401 More actions · Answer 1

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.

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)