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
January 16, 2020 at 10:49 pm
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
Change is inevitable... Change for the better is not.
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
Change is inevitable... Change for the better is not.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy