April 14, 2009

SQL SERVER: SQL Query To Find Most used Tables



We have very large database and today we want to search the tables which are used mostly.

Means tables which are used in Procedures, Constraints, Views, Triggers etc.

I know this is very strange requirement, but we need to do this.

So, I tried to make an query which will help me to find out the top most tables used in other objects as I mentioned

Let me share that sp with all of you: 
SELECT TableName, COUNT(*) 
FROM ( 
Select Distinct 
o.Name 'TableName', 
op.Name 'DependentObject' 
From SysObjects o 
INNER Join SysDepends d ON d.DepId = o.Id 
INNER Join SysObjects op on op.Id = d.Id 
Where o.XType = 'U' 
Group by o.Name, o.Id, op.Name 
) x 
GROUP BY TableName 
ORDER BY 2 desc

So, I made my life easy, by using this. I can get the list if Tables which are used most.





Let me know if it helps you in any way.






6 comments:

  1. ravi4uJuly 13, 2011 at 10:01 PM

    yes It helped me to find the tables that mostly used.

    ReplyDelete
  2. yunus karadağOctober 1, 2012 at 8:48 PM

    Thank you sir for this script.

    ReplyDelete
  3. AnonymousJuly 6, 2013 at 5:06 AM

    Very helpful...thanks!

    ReplyDelete
  4. Julian CastiblancoApril 24, 2014 at 6:31 PM

    thats so good, i complemented your article querying the sys.dm_db_index_usage_stats DMV, because you could cover the adhoc queries

    ReplyDelete
  5. UnknownAugust 4, 2015 at 12:16 PM

    hi
    i use this select but i have a problem.
    how can i save this information before server reset or is shutdown.
    because information in sys.dm_db_index_usage_stats be deleted in server rest.
    please help me.
    thank you

    ReplyDelete