Voici une petite fonction qui retourne la liste des colonnes composant un index dans l’ordre de leur création.
Pour SQL Server 2005 :
CREATE FUNCTION dbo.F_INDEX_COLS (@OID INT, @IID INT)
RETURNS NVARCHAR(max)
AS
BEGIN
DECLARE @RETVAL NVARCHAR(max);
SET @RETVAL = '';
SELECT @RETVAL = @RETVAL + '[' + c.name +']'
+ CASE is_descending_key
WHEN 1 THEN ' DESC'
ELSE ' ASC'
END + ', '
FROM sys.index_columns AS ic
INNER JOIN sys.columns AS c
ON ic.column_id = c.column_id
AND ic.object_id = c.object_id
WHERE ic.object_id = @OID
AND ic.index_id = @IID
ORDER BY key_ordinal;
RETURN SUBSTRING(@RETVAL, 1, LEN(@RETVAL) - 1);
END
Pour SQL Server 2008 :
CREATE FUNCTION dbo.F_INDEX_COLS (@OID INT, @IID INT, @KEY BIT = 1)
RETURNS NVARCHAR(max)
AS
BEGIN
DECLARE @RETVAL NVARCHAR(max);
SET @RETVAL = '';
SELECT @RETVAL = @RETVAL + '[' + c.name +']'
+ CASE is_descending_key
WHEN 1 THEN ' DESC'
ELSE ''
END + ', '
FROM sys.index_columns AS ic
INNER JOIN sys.columns AS c
ON ic.column_id = c.column_id
AND ic.object_id = c.object_id
WHERE ic.object_id = @OID
AND ic.index_id = @IID
AND is_included_column @KEY
ORDER BY key_ordinal;
RETURN CASE
WHEN @RETVAL = '' THEN ''
ELSE SUBSTRING(@RETVAL, 1, LEN(@RETVAL) - 1)
END;
END
Si @KEY = 1 retourne la composition de la clef. Sinon, retourne la composition de l’option INCLUDE.
--------
Frédéric Brouard, SQLpro - ARCHITECTE DE DONNÉES, http://sqlpro.developpez.com/
Expert bases de données relationnelles et langage SQL. MVP Microsoft SQL Server
www.sqlspot.com : modélisation, conseil, audit, optimisation, tuning, formation
* * * * * Enseignant CNAM PACA - ISEN Toulon - CESI Aix en Provence * * * * *
