Identify fragmentation for all the indexes in a database


USE AdventureWorks;

SELECT   ‘dbcc showcontig (‘ + convert(VARCHAR,i.[object_id]) + ‘,’ + — table id 

          convert(VARCHAR,i.index_id) + ‘) — ‘ + — index id 

          s.name + — schema name

          ‘.’ + object_name(i.[object_id]) + — table name 

          ‘.’ + i.name — index name

FROM     sys.schemas s

         JOIN sys.objects o

           ON (s.schema_id = o.schema_id)

         JOIN sys.indexes i

           ON (o.[object_id] = i.[object_id])

WHERE    o.TYPE = ‘U’

         AND i.TYPE IN (1,2)

ORDER BY s.name,

         object_name(i.[object_id]),

         i.index_id

GO

/* Partial results

dbcc showcontig (642101328,1) —
— Sales.SalesOrderDetail.PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID

dbcc showcontig (642101328,2) — Sales.SalesOrderDetail.AK_SalesOrderDetail_rowguid

dbcc showcontig (642101328,3) — Sales.SalesOrderDetail.IX_SalesOrderDetail_ProductID

dbcc showcontig (754101727,1) — Sales.SalesOrderHeader.PK_SalesOrderHeader_SalesOrderID

dbcc showcontig (754101727,2) — Sales.SalesOrderHeader.AK_SalesOrderHeader_rowguid

dbcc showcontig (754101727,3) —
— Sales.SalesOrderHeader.AK_SalesOrderHeader_SalesOrderNumber

*/

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: