SQL SERVER 2000 - 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(4000) 
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 dbo.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) 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 
  
-----------------------------------------------------------------