October 2, 2009 at 11:55 am
While trying to determine when a specific table was last updated, an error message appeared (using SQL Server Management Studio).
Error:
Msg 208, Level 16, State 1, Line 1
Invalid object name 'sys.dm_db_index_usage_stats'.
See code below:
---------
CODE
SELECT OBJECT_NAME(OBJECT_ID) AS DatabaseName, last_user_update,*FROM sys.dm_db_index_usage_statsWHERE database_id = DB_ID('MasteryNet')AND OBJECT_ID=OBJECT_ID('LAST_RECORDS'
---------
I discovered that the table 'sys.dm_db_index_usage_stats' doesn't exist, if it did, I would have found it by now.
What else can I try in order to find out when the LAST_RECORDS table was last updated? ..I've spent alot of time searching online without any success....at this point, any suggestions or assistance would be a great help!
Thanks!!
October 2, 2009 at 12:25 pm
ready2drum (10/2/2009)
While trying to determine when a specific table was last updated, an error message appeared (using SQL Server Management Studio).Error:
Msg 208, Level 16, State 1, Line 1
Invalid object name 'sys.dm_db_index_usage_stats'.
See code below:
---------
CODE
SELECT OBJECT_NAME(OBJECT_ID) AS DatabaseName, last_user_update,*FROM sys.dm_db_index_usage_statsWHERE database_id = DB_ID('MasteryNet')AND OBJECT_ID=OBJECT_ID('LAST_RECORDS'
---------
I discovered that the table 'sys.dm_db_index_usage_stats' doesn't exist, if it did, I would have found it by now.
What else can I try in order to find out when the LAST_RECORDS table was last updated? ..I've spent alot of time searching online without any success....at this point, any suggestions or assistance would be a great help!
Thanks!!
sys.dm_db_index_usage_stats is a dynamic management view. It wouldn't be listed in the tables section of the db. If you have a 2005 or 2008 server, it's there. If you're connected to 2000, it won't be there.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 2, 2009 at 12:54 pm
Okay...so if I'm using SQL Server 2005, where do go to execute that command? when you say it's in there, are you referring to the dynamic management view (sys.dm_db_index_usage_stats)? I'll look for it, but I want to make sure that I'm looking in the right place.
Thanks!
October 2, 2009 at 1:02 pm
in sql management studio, you can expand your database, then expand views, then expand system_views..
thats where it'll be..
you'll have to query it from a New Query window though..
USE myDB
GO
SELECT * FROM sys.dm_db_index_usage_stats
GO
Not 100% convinced what you need is in this table, or maybe I'm misunderstanding what you're needing.. but.. thats where it is
--------------------------
I long for a job where my databases dont have any pesky users accessing them 🙂
October 2, 2009 at 1:20 pm
I appreciate your help on this issue...I'll take your suggestion and see where it leads me...if I run into any trouble, I'll reply back to this forum topic.
Thank you for your help!
October 2, 2009 at 1:29 pm
ready2drum (10/2/2009)
Okay...so if I'm using SQL Server 2005, where do go to execute that command? when you say it's in there, are you referring to the dynamic management view (sys.dm_db_index_usage_stats)? I'll look for it, but I want to make sure that I'm looking in the right place.Thanks!
Since it's a system wide thing, you don't really need to look it up to use it. You can run it from any database in the system. It includes the database id so that you can filter it to the appropriate db.
But, I don't think it's necessarily going to deliver what you're looking for. For example, the update values are based on inserts, updates and deletes, not simply updates. There's no way to differentiate. Also, the data is only good since the last time the server was started or the database was started or attached (I assume including restores, but I'd have to test that to be sure). It could be off. Also, what happens when a table doesn't have indexes (not a standard in anyone's systems you hope, but it happens).
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 26, 2011 at 9:49 am
After I had this issue too, I changed the DV name from UPPERCASE to LOWERCASE:
sys.dm_db_index_usage_stats
It worked for me.
February 25, 2015 at 1:28 pm
Yes, that error occurs when your database collation is set to case sensitive.
Msg 208, Level 16, State 1, Line 1
Invalid object name 'SYS.DM_DB_INDEX_USAGE_STATS'.
To resolve, just CTRL+SHIFT+L to convert the text to lower case which is the right case to access the system view.
Tung Dang
Azure and SQL Server Solutions Provider
DataZip
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply