Sql server 2005 - creer des indexes sur un champ existant dans toutes les tables de la base

Commenter

Contenu du snippet

Dans certains cas, on peut trouver une conception des tables d'une base très générique (surtout en passant par un outil de génération automatique comme Hibernate ou Nhibernate). Ainsi, on peut vouloir optimiser la base en créant des indexes sur certains de ces champs génériques.
Ce script vous permet d'effectuer ce travail en testant l'existance et respectant les normes de nommage des indexes.

Il suffit donc de l'adapter à votre cas et à vos règles internes.

Source / Exemple : 


-----------------------------------------------------------------
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

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

Conclusion :


Bon coding

Romelard Fabrice [MVP]

A voir également

S'inscrire maintenant

Vous n'êtes pas encore membre ?

inscrivez-vous, c'est gratuit et ça prend moins d'une minute !

Les membres obtiennent plus de réponses que les utilisateurs anonymes.

Le fait d'être membre vous permet d'avoir un suivi détaillé de vos demandes et codes sources.

Le fait d'être membre vous permet d'avoir des options supplémentaires.

Du même auteur (cs_fabrice69)