What I'm looking to do is review our databases and lock down any auto-shrink settings, as well as get a handle on which databases/tables are highly fragmented.

Is there a particular script I can run to get a good idea per database?

I know I can run the following on a per table level (SQL Server 2005 at least):


But what can I run to show me all tables in a database?


1 Answer 1


For checking fragmentation in 2005/2008 you can use the following script. You need to set the @DB and @Table values. If you define those as NULL then it will run on all databases and/or all tables. If you do a single db make sure you execute in that DB's context (USE MyDB).

    object_name(IPS.object_id) AS [Table Name], 
    SI.name AS [Index Name], 
        CASE IPS.Index_type_desc
            WHEN 'CLUSTERED INDEX' THEN 'Clustered'
            ELSE 'Non-Clustered'
        END AS 'Index Type', 
    IPS.avg_fragmentation_in_percent as 'Avg Fragmentation (%)', 
    IPS.avg_fragment_size_in_pages as 'Avg Frag Size (pages)',
    IPS.page_count as 'Page Count', 
    IPS.forwarded_record_count as 'Forwarded Records',
    --IPS.avg_page_space_used_in_percent as 'Avg Page Space Used (%)', 
    --IPS.record_count as 'Record Count', 
    --IPS.ghost_record_count as 'Ghost Record Count',
    IPS.fragment_count as 'Fragment Count'
FROM sys.dm_db_index_physical_stats
        NULL , 
    ) as IPS
JOIN sys.indexes as SI WITH (nolock) 
    ON IPS.object_id = SI.object_id 
    AND IPS.index_id = SI.index_id
ORDER BY 1,3,5

For autoshrink you can just check master.sys.databases:

select * from master.sys.databases
where is_auto_shrink_on = 1
  • +1, thanks just what I need. My 'only' however is, I see some databases return no result at all, is that only because they need to have physical stats in the sys db?
    – Jakub
    Mar 7, 2012 at 15:47
  • @Jakub - yep. This script also ignores heaps (unindexed tables), so if there are no indexed tables in a DB it won't show up either.
    – JNK
    Mar 7, 2012 at 15:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.