Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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):

DBCC SHOWCONTIG ('DB.TABLE');

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

Thanks

share|improve this question

1 Answer 1

up vote 6 down vote accepted

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).

SELECT 
    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
    (
        db_id(@DB), 
        OBJECT_ID(@Table), 
        NULL,
        NULL , 
        'LIMITED'
    ) 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
share|improve this answer
    
+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 '12 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 '12 at 15:48

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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