SQL SERVER 2005 - DETRUIRE TOUS LES INDEXES APPLIQUES SUR UN MEME CHAMP DANS TOUTES LES TABLES

-- =============================================
-- Author:		ROMELARD FABRICE
-- Create date: 18-10-2006
-- Description: Script de destruction des Indexes avec un champ choisi
-- =============================================
---------------------------------------------------------------------------------------------------------------------------------------
USE [MaBase]

---------------------------------------------------------------------------------------------------------------------------------------
-- Spécification du champ qui doit être indexé
---------------------------------------------------------------------------------------------------------------------------------------
DECLARE @NOM_CHAMP		AS VARCHAR(50)
SET @NOM_CHAMP = 'MonChamp'

---------------------------------------------------------------------------------------------------------------------------------------
DECLARE @SQL						AS VARCHAR(MAX)

---------------------------------------------------------------------------------------------------------------------------------------
-- Déclaration du Curseur de sélection
---------------------------------------------------------------------------------------------------------------------------------------
DECLARE TESTCURSEUR CURSOR 
   FOR SELECT 'DROP INDEX ['+ IDX.name +'] ON ['+ SCH.name +'].['+ OBJ.name +'] WITH (ONLINE = OFF)' AS SQLCMD
		FROM  sys.index_columns IDXC INNER JOIN sys.objects OBJ ON IDXC.object_id = OBJ.object_id 
		INNER JOIN sys.schemas SCH ON SCH.schema_id = OBJ.schema_id
		INNER JOIN sys.indexes IDX ON (IDXC.object_id = IDX.object_id AND IDXC.index_id = IDX.index_id)
		INNER JOIN sys.columns COL ON (IDXC.column_id = COL.column_id AND OBJ.object_id = COL.object_id)
		WHERE (OBJ.type = 'U' OR OBJ.type = 'V') AND COL.name = @NOM_CHAMP

---------------------------------------------------------------------------------------------------------------------------------------
-- Déclaration du Curseur sur la liste des indexes
OPEN TESTCURSEUR 
FETCH NEXT FROM TESTCURSEUR INTO @SQL
   
WHILE (@@FETCH_STATUS >= 0)
	BEGIN 
		PRINT (@SQL)
		--EXECUTE (@SQL)

		FETCH NEXT FROM TESTCURSEUR 
		INTO @SQL 
	END

---------------------------------------------------------------------------------------------------------------------------------------
-- Fermeture du Curseur 
CLOSE TESTCURSEUR 
  
-- Libération de la mémoire prise par le Curseur 
DEALLOCATE TESTCURSEUR

---------------------------------------------------------------------------------------------------------------------------------------