2PTTechnology

declare @dbid smallint

declare @db varchar(100)

set @db = 'MyDatabaseName'

select @dbid = dbid from sys.sysdatabases where name = @db

select

DB_NAME() AS DatbaseName

, 'DBCC DBREINDEX(''' + OBJECT_NAME(s.[object_id]) + ''')' AS Sql_00

, 'ALTER INDEX ' + i.name + ' ON ' + @db + '.dbo.' + OBJECT_NAME(s.[object_id]) + ' REBUILD WITH (ONLINE = ON, SORT_IN_TEMPDB = OFF)' as Sql_05

, OBJECT_NAME(s.[object_id]) AS TableName

, i.name AS IndexName

, ROUND(s.avg_fragment_size_in_pages,2) as PagesPerFragment

, ROUND(s.avg_page_space_used_in_percent,2) as PageDensity

, ROUND(s.avg_fragmentation_in_percent,2) AS [Fragmentation%]

, s.page_count

from

sys.dm_db_index_physical_stats(@dbid, null, null,NULL,'DETAILED') s

INNER JOIN sys.indexes i ON s.[object_id] = i.[object_id]

AND s.index_id = i.index_id

WHERE s.database_id = DB_ID()

AND i.name IS NOT NULL

AND OBJECTPROPERTY(s.[object_id], 'IsMsShipped') = 0

AND s.avg_fragmentation_in_percent > 30 -- i only care about 30% or higher Fragmentation