Blog Detail

21 Apr 2009
Tejas Shah
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.

About me


Tejas Shah

Microsoft Certified Professional Expert. I have 14+ years of experience in Design and Develop 100+ .Net applications using Asp.Net, C#, Asp.Net Core, VB.Net, SQL Server, MVC, AngularJS, JavaScript, Azure, HTML5 and CSS3.