September 25, 2012 at 10:54 pm
Hi,
I wanted to know this just to increased my information as i am a newbie. I found out this on internet but couldn't find out any satisfactory answer to this question. I would like to know the table which is the most and least used in the database.
September 26, 2012 at 12:56 am
Hope this helps you..
SELECT TableName, COUNT(*) [Dependency 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
🙂
Kivan G
September 26, 2012 at 1:03 am
Do not trust sysdepends, it lies like a rug.
There is no easy method to be sure of how often a table is used, particularly for select statements. You'd start with tracing proc calls and ad hoc queries and drill down on their counts, which will eventually lead you to the tables. You would then extrapolate from there.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
September 26, 2012 at 1:24 am
On 2008, use sys.dm_sql_referenced_entities and sys.dm_sql_referencing_entities. They're a hell of a lot more reliable that sysdepends.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 26, 2012 at 4:04 am
It does depend on how you defined "used". You could also take a look at the index usage statistics to see how often a clustered index (which is a table) or a heap (table w/o a clustered index) is accessed. This is a really simple example:
SELECTOBJECT_NAME(object_id, database_id) AS ObjectName,
ddius.user_lookups,
ddius.user_scans,
ddius.user_seeks,
ddius.user_updates
FROMsys.dm_db_index_usage_stats AS ddius
WHEREOBJECTPROPERTY(ddius.object_id, 'IsUserTable') = 1;
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply