ÿþ/****** Object:  StoredProcedure [dbo].[GetStatsForIndexes]    Script Date: 09/30/2009 10:02:28 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- =============================================

-- Author:		John Sterrett

-- Create date: 9/18/2009

-- Description:	Pulls stats towards rebuilding indexes

-- =============================================

ALTER PROCEDURE [dbo].[GetStatsForIndexes] 

	@PageCount INT = 100



AS

BEGIN

	-- SET NOCOUNT ON added to prevent extra result sets from

	-- interfering with SELECT statements.

	SET NOCOUNT ON;

	

	-- Declare varables

	DECLARE @dbID INT, @dbName VARCHAR(128), @SQL NVARCHAR(MAX)

	

	-- Create a temp table to store all active databases

	CREATE TABLE #databaseList

    (

          databaseID        INT

        , databaseName      VARCHAR(128)

    );

    

    -- we only want non-system databases who are currenlty online

    INSERT INTO #databaseList (databaseID, databaseName)

    SELECT d.database_id, d.name FROM sys.databases d where d.[state] = 0 and d.database_id > 4

   

    

    -- Loop through all databases 

   	WHILE (SELECT COUNT(*) FROM #databaseList) > 0  BEGIN

   	

   		-- get a database id

		SELECT TOP 1 @dbID = databaseID, @dbName = databaseName

		FROM #databaseList;

			

			SET @SQL = 'INSERT INTO DBA_Tools.dbo.IDX_FRAG (databaseName, ObjectName, indexName, partitionNumber, fragmentation, fill_factor, is_padded, type_desc, page_count, [date])

				SELECT

				  db.name AS databaseName

				, obj.name AS ObjectName

				, idx.name AS indexName

				, ps.partition_number AS partitionNumber

				, ps.avg_fragmentation_in_percent AS fragmentation

				,idx.fill_factor

				,idx.is_padded

				,idx.type_desc

				, ps.page_count

				, GETDATE() as [date]

			FROM sys.databases db

			  INNER JOIN sys.dm_db_index_physical_stats ('+CAST(@dbID AS VARCHAR(10))+', NULL, NULL , NULL, N''Limited'') ps

				  ON db.database_id = ps.database_id

			  INNER JOIN '+ @dbName+'.sys.objects obj ON obj.object_id = ps.object_id

			  INNER JOIN '+ @dbName+'.sys.indexes idx ON idx.index_id = ps.index_id AND idx.object_id = ps.object_id

			WHERE ps.index_id > 0 

			   AND ps.page_count > 100 

			ORDER BY page_count desc

			OPTION (MaxDop 1);'

		

		EXECUTE sp_executesql @SQL	

		-- remove the database from the databases table

		DELETE FROM #databaseList WHERE databaseID = @dbID

		

		-- get the next database in the databases table

		SELECT TOP 1 @dbID = databaseID, @dbName = databaseName

		FROM #databaseList;

		

	END

	-- temp table is no longer needed, so we will kill it.

	DROP TABLE #databaseList;

END