SQL SERVER 2005 - LISTER LES INDEXES AVEC LE NOMBRE DE LIGNE DE CHACUN

----------------------------------------------------------------- 
USE [Strip3Prod] 
  
----------------------------------------------------------------- 
-- Spécification du champ qui doit être indexé 
----------------------------------------------------------------- 
DECLARE @NOM_CHAMP AS VARCHAR(50) 
SET @NOM_CHAMP = 'MonNomDeChamp' 
  
----------------------------------------------------------------- 
DECLARE @SQLRequest AS VARCHAR(MAX) 
DECLARE @SQL AS VARCHAR(MAX) 
  
----------------------------------------------------------------- 
-- Déclaration du Curseur de sélection 
----------------------------------------------------------------- 
DECLARE TESTCURSEUR CURSOR 
   FOR		SELECT 
	'SELECT '''+ IDX.name +''' AS INDEX_NAME, '''+ OBJ.name +''' AS OBJECT_NAME, '''+ COL.name +''' AS COLUMN_NAME, COUNT(DISTINCT ['+ COL.name +']) NB_ROWS FROM ['+ SCH.name +'].['+ OBJ.name +'] '

FROM sys.index_columns IDXC 
INNER JOIN sys.objects OBJ 
        ON IDXC.object_id = OBJ.object_id 
			--AND OBJ.name = 'Document')
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')
ORDER BY OBJ.name
  
----------------------------------------------------------------- 
-- Déclaration du Curseur sur la liste des Bases de données 
OPEN TESTCURSEUR 
FETCH NEXT FROM TESTCURSEUR INTO @SQLRequest
    
WHILE (@@FETCH_STATUS >= 0) 
    BEGIN 
         
        SET @SQL = @SQLRequest
		PRINT @SQL

--        EXECUTE (@SQL) 
  
        FETCH NEXT FROM TESTCURSEUR 
        INTO @SQLRequest 
    END 
  
----------------------------------------------------------------- 
-- Fermeture du Curseur 
CLOSE TESTCURSEUR 
   
-- Libération de la mémoire prise par le Curseur 
DEALLOCATE TESTCURSEUR 
  
-----------------------------------------------------------------