MD3.RepopulateIndexes.sql 1.14 KB
IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id('MD3.RepopulateIndexes'))
BEGIN
	DROP PROCEDURE MD3.RepopulateIndexes
END
GO

CREATE Procedure MD3.RepopulateIndexes

AS  
BEGIN

/*
	Blows away all entries in MD3.Indexes table, EXCEPT entries with DropIndex.  We need to maintain this "state"
	Repopulates the table with current index information from the current database


*/
    SET NOCOUNT ON  


	DELETE FROM MD3.Indexes WHERE DropIndex = 0



	INSERT INTO MD3.Indexes
		(IndexName,SchemaName,TableName,KeyColumns,IncludedColumns,IsClustered,IsUnique,FGorPartitionScheme,IdxFillFactor,IsPadded,CompressionSetting,DropIndex)
	SELECT 
		Const.IndexName,SCHEMA_NAME(o.Schema_id),o.Name, Const.IndexKeys,Const.IncludedColumns,Const.IsClustered,Const.IsUnique,Const.FGorPartitionScheme,
		Const.IdxFillFactor,Const.IsPadded,Const.CompressionSetting,0
	FROM sys.objects AS o
	JOIN sys.schemas AS s 
		ON o.schema_id = s.schema_id
	CROSS APPLY MD3.f_GetIndexes(s.Name, o.Name) as Const
	WHERE o.type = 'U'
	AND s.name <> 'MD3'
	AND Const.IsPrimaryKey = 0
	ORDER BY 
		SCHEMA_NAME(o.schema_id),
		o.Name


	
END
GO