Tuesday, 28 August 2012

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


No comments:

Post a Comment