Script to Check Fragmentation of Database

---- To check Fragementation of current Database in SQL Server 2005/2008/2008R2/2012

DECLARE @DBID INT 

SELECT @DBID = DB_ID() 

--- Identifying the High / Low Fragmentation of Table(s) in the active Database 

SELECT OBJECT_NAME([OBJECT_ID]) 'TABLE NAME',INDEX_TYPE_DESC 'INDEX TYPE',IND.[NAME],CASE WHEN AVG_FRAGMENTATION_IN_PERCENT <30 THEN 'To Be Re-Organized' ELSE 'To Be Rebuilt' END 'ACTION TO BE TAKEN' ,AVG_FRAGMENTATION_IN_PERCENT '% FRAGMENTED' 

FROM sys.dm_db_index_physical_stats(@DBID, NULL, NULL, NULL, NULL) JOIN sys.sysindexes IND 

ON (IND.ID =[OBJECT_ID] AND IND.INDID = INDEX_ID) 

WHERE AVG_FRAGMENTATION_IN_PERCENT > 0 

AND DATABASE_ID = @DBID 

AND IND.FIRST IS NOT NULL 

AND IND.[NAME] IS NOT NULL 

ORDER BY 5 DESC

---- To check Fragmentation of databases in SQL Server 2000

----Display the full result set for every index on every table in a database

----This example returns a full table result set for every index on every table in the XXXXXX database

USE XXXXXX

DBCC SHOWCONTIG WITH TABLERESULTS, ALL_INDEXES

---This example returns a full table result set for every index on every table in all database

EXECUTE master.sys.sp_MSforeachdb 'USE [?]; DBCC SHOWCONTIG WITH TABLERESULTS, ALL_INDEXES;'

----- SQL Server 2000 version - DBCC SHOWCONTIG for index fragmentation

------------

USE Northwind;

SELECT   'DBCC SHOWCONTIG (' + convert(VARCHAR,o.id) + ',' +

convert(VARCHAR,i.indid) + ') -- ' + object_name(o.id) + -- table name 

          '.' + i.name -- index name 

FROM     sysobjects o

         JOIN sysindexes i

           ON (o.id = i.id)

WHERE    o.xtype = 'U'

         AND i.rows > 0

ORDER BY object_name(o.id),

         i.indid

GO