October 17, 2014 at 10:08 am
Hello,
I have this t-sql code which will get some table stats on one database at a time, I was wondering how I would get it to loop through all databases so it will pull the stats from all tables in all databases. Here is my code:
Select object_schema_name(UStat.object_id)
+ '.' + object_name(UStat.object_id) As [Object Name]
,Case
When Sum(User_Updates + User_Seeks + User_Scans + User_Lookups) = 0 Then Null
Else Cast(Sum(User_Seeks + User_Scans + User_Lookups) As Decimal)
/ Cast(Sum(User_Updates
+ User_Seeks
+ User_Scans
+ User_Lookups) As Decimal(19,2))
End As [Proportion of Reads]
, Case
When Sum(User_Updates + User_Seeks + User_Scans + User_Lookups) = 0 Then Null
Else Cast(Sum(User_Updates) As Decimal)
/ Cast(Sum(User_Updates
+ User_Seeks
+ User_Scans
+ User_Lookups) As Decimal(19,2))
End As [Proportion Of Writes]
, Sum(User_Seeks + User_Scans + User_Lookups) As [Total Read Ops]
, Sum(User_Updates) As [Total Write Ops]
, Max(last_user_seek) As [Last Seek]
, Max(last_user_scan) As [Last Scan]
, Max(last_user_lookup) As [Last Lookup]
, Max(last_user_update) As [Last Update]
, t.create_date As [Create Date]
From sys.dm_db_Index_Usage_Stats As UStat
Join Sys.Indexes As I
On UStat.object_id = I.object_id
And UStat.index_Id = I.index_Id
Join sys.tables As T
On T.object_id = UStat.object_id
Where I.Type_Desc In ( 'Clustered', 'Heap' )
Group By UStat.object_id, t.create_date
Order By object_schema_name(UStat.object_id)
+ '.' + object_name(UStat.object_id)
Thanks,
Sam
October 17, 2014 at 10:12 am
You can use the undocumented and usupported stored procedure sp_msforeachdb. Actually it has some issues (every now and then it skips a database, to name one), so you can use my replacement you can find here: http://spaghettidba.com/2011/09/09/a-better-sp_msforeachdb/
-- Gianluca Sartori
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply