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(*) 
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.


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

  2. Very helpful...thanks!

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

  4. 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