[SQL Server] Calculer indensité des index
DROP TABLE IF EXISTS #densite_colonnes
CREATE TABLE #densite_colonnes
(
num_lig INT IDENTITY(1,1)
,nom_table VARCHAR(75) NULL
,nom_colonne VARCHAR(75) NULL
,densite FLOAT NULL
)
SELECT nom_table,nom_colonne,'INSERT INTO #densite_colonnes (nom_table,nom_colonne,densite)
SELECT ''' + nom_table + ''' AS nom_table,''' + nom_colonne + ''' AS nom_colonne,(1.000000/COUNT(' + QUOTENAME( nom_colonne ) + ')) AS densite FROM (
SELECT DISTINCT ' + QUOTENAME( nom_colonne) + ' FROM [dbo].' + nom_table + ') T1'
FROM (
SELECT OBJECT_NAME(T1.object_id) as nom_table ,T1.name as nom_index,T3.name as nom_colonne FROM sys.indexes T1
JOIN sysindexkeys T2 ON T2.id=T1.object_id AND T2.indid=T1.index_id
JOIN syscolumns T3 ON T3.id = T2.id AND T3.colid = T2.colid
JOIN sys.index_columns T4 on T4.object_id = T2.ID and T4.index_id = T2.indid and T4.column_id = T3.colid
JOIN sys.objects T5 ON T5.object_id=T1.object_id
WHERE T5.[type]='U'
AND OBJECT_NAME(T1.object_id)='MaTable'
) R1
SELECT * FROM #densite_colonnes
ORDER BY nom_table ASC,densite ASC