SQL SERVER 2005 - CREER DES INDEXES SUR UN CHAMP EXISTANT DANS TOUTES LES TABLES DE LA BASE

-----------------------------------------------------------------
USE [MaDataBase]

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

-----------------------------------------------------------------
DECLARE @TCompleteName AS VARCHAR(500)
DECLARE @SQL AS VARCHAR(MAX)
DECLARE @NomIDX AS VARCHAR(500)

-----------------------------------------------------------------
-- Déclaration du Curseur de sélection
-----------------------------------------------------------------
DECLARE TESTCURSEUR CURSOR 
   FOR SELECT 
	'[IDX_'+ UPPER(TAB.TABLE_NAME) +'_'+ UPPER(@NOM_CHAMP) +']' AS TIndex,
	'['+ TAB.TABLE_SCHEMA +'].['+ TAB.TABLE_NAME +']' AS TCompleteName
FROM 
	INFORMATION_SCHEMA.COLUMNS COL
INNER JOIN INFORMATION_SCHEMA.TABLES TAB
	ON (TAB.TABLE_SCHEMA = COL.TABLE_SCHEMA AND TAB.TABLE_NAME = COL.TABLE_NAME)
LEFT OUTER JOIN sys.sysindexes IDX
	ON IDX.name = 'IDX_'+ UPPER(TAB.TABLE_NAME) +'_'+ UPPER(@NOM_CHAMP)	
WHERE 
	COL.COLUMN_NAME = @NOM_CHAMP
AND
	TAB.TABLE_TYPE = 'BASE TABLE'
AND
	IDX.name IS NULL

-----------------------------------------------------------------
-- Déclaration du Curseur sur la liste des Bases de données 
OPEN TESTCURSEUR 
FETCH NEXT FROM TESTCURSEUR INTO @NomIDX, @TCompleteName 
   
WHILE (@@FETCH_STATUS >= 0)
	BEGIN 
		
		SET @SQL =  'CREATE NONCLUSTERED INDEX '+ @NomIDX +' ON '+ @TCompleteName +'(['+ @NOM_CHAMP +'] ASC) '
		SET @SQL = @SQL +'WITH (STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, '
		SET @SQL = @SQL +'IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = OFF) '
		SET @SQL = @SQL +'ON [PRIMARY]'

		EXECUTE (@SQL)

		FETCH NEXT FROM TESTCURSEUR 
		INTO @NomIDX, @TCompleteName 
	END

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

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