fragmentation report for all databases / tables

So I needed to report on all the tables in the various database and see their fragmentation over time. With a little help from a new DMV Function I can do it.

here ya go


Create Procedure AdminFragmentationReportAllDB

as



SET NOCOUNT ON

/*all databases*/
Declare @databases table ( DatabaseID bigint, name NVarchar(255) )
Insert Into @databases (DatabaseID,name)
Select database_id,name
From sys.databases
WHERE name not in ('Master','TEMPDB','MSDB','MODEL')

/*for each database nab the tables*/
Declare @tables table ( DatabaseName NVarchar(255), TableName NVarchar(255) )

Declare @DatabaseName NVarchar(255)
Declare @TableLookupSQL NVarchar(Max)

DECLARE tbl CURSOR FOR
SELECT Name
From @databases
OPEN tbl;

FETCH NEXT FROM tbl
INTO @DatabaseName

WHILE @@FETCH_STATUS = 0
BEGIN

SELECT @TableLookupSQL = N''
SELECT @TableLookupSQL = @TableLookupSQL +
N'
Select '''+@DatabaseName+''' as DatabaseName, s.name+''.''+t.name as TableName
From ['+@DatabaseName+'].sys.tables t
JOIN ['+@DatabaseName+'].sys.schemas s on t.schema_id = s.schema_id
'
INSERT INTO @tables (DatabaseName,TableName)
EXEC sp_executesql @TableLookupSQL


FETCH NEXT FROM tbl
INTO @DatabaseName
END
CLOSE tbl;
DEALLOCATE tbl;


/*have all databases and tables
now to check each*/
DECLARE @Dbname NVarchar(255), @Tblname NVarchar(255)
DECLARE @FragSQL NVarchar(max)
DECLARE @FragResult Table (DBName NVarchar(255), TBLName NVarchar(255), objectid bigint, indexid bigint, avg_fragmentation_in_percent numeric(10,5), page_count bigint)

DECLARE Frag CURSOR FOR
SELECT DatabaseName,TableName
FROM @tables

OPEN Frag;

FETCH NEXT FROM Frag
INTO @Dbname,@Tblname

WHILE @@FETCH_STATUS = 0
BEGIN

Select @FragSQL = N''
Select @FragSQL = @FragSQL + N'
SELECT '''+@Dbname+''' as DBName, '''+@Tblname+''' as TBLName,object_id, index_id, avg_fragmentation_in_percent, page_count
FROM sys.dm_db_index_physical_stats(DB_ID('''+@Dbname+'''), OBJECT_ID('''+@Tblname+'''), NULL, NULL, NULL);
'
INSERT INTO @FragResult (DBName,TBLName,objectid,indexid,avg_fragmentation_in_percent,page_count)
EXEC SP_EXECUTESQL @FragSQL

FETCH NEXT FROM Frag
INTO @Dbname,@Tblname
END
CLOSE Frag;
DEALLOCATE Frag;




Select DBName,TBLName,objectid,indexid,avg_fragmentation_in_percent,page_count
FROM @FragResult