Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
manju3606
Yak Posting Veteran
78 Posts |
 Posted - 2011-11-03 : 06:13:32
|
| Hi to all,I am using below query to get most used table list and i am getting result also good but what i want to do is i want to store the result in new table and i want to add 'user_scans' column in query so please help me with this declare @dbid int select @dbid = DB_ID('test' ) ; WITHcteAS(SELECT [object_id], last_user_seek, last_user_scan, last_user_lookup, last_user_updateFROM sys .dm_db_index_usage_stats WHERE database_id= @dbid)SELECT[Schema]= OBJECT_SCHEMA_NAME([object_id],@dbid ),[Table_Or_View]= OBJECT_NAME([object_id],@dbid), last_read= MAX(last_read), last_write= MAX(last_write) FROM(SELECT [object_id], last_user_seek, NULL FROM cte UNION ALL SELECT [object_id], last_user_scan, NULL FROM cte UNION ALL SELECT [object_id], last_user_lookup, NULL FROM cte UNION ALL SELECT [object_id], NULL, last_user_update FROM cte )AS x ([object_id], last_read, last_write )GROUPBYOBJECT_SCHEMA_NAME ([object_id],@dbid), OBJECT_NAME ([object_id],@dbid) ORDERBY 1,2; Thanks Manju |
|
|
DonAtWork
Master Smack Fu Yak Hacker
2167 Posts |
Posted - 2011-11-03 : 07:24:04
|
| 1) Formatting is bad.2) Why not just make this a view and call it when you need the data?http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspxHow to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspxFor ultra basic questions, follow these links.http://www.sql-tutorial.net/ http://www.firstsql.com/tutor.htm http://www.w3schools.com/sql/default.asp |
 |
|
|
|
|
|
|
|
most used tables query