Auflisten aller Indizierten Felder aller Tabellen (ohne Systemtabellen) ohne PrimaryKeys

Question

Hallo

wie kann ich alle Indizes einer Datenbank auflisten (mit Tabellenname) mit einer SQL Abfrage

ohne das die PrimaryKeys mit aufgelistet werden

Answer 1

Ich glaube ich hab es selber

select T.name table_name, I.name index_name, C.name column_name, i.type from sys.tables T
 left outer join (select * from sys.indexes) I on T.object_id = I.object_id
 left outer join sys.index_columns IC on T.object_id = IC.object_id and I.index_id = IC.index_id
 left outer join sys.columns C on T.object_id = C.object_id and IC.column_id = C.column_id
 where T.type = 'U' and i.type = 2
 order by T.name

Answer 2

Hallo Michael,

der Index-Type sagt nichts darüber aus ob es auch der Primärschlüssel ist.
Tabellennamen werden in einem Schema verwaltet (und Namen sind je Schema eindeutig).
Bei Indizes spielt die Reihenfolge der Spalten eine Rolle (und ihre Sortierung).

SELECT 
	SCHEMA_NAME(t.schema_id) AS SchemaName,
	t.name AS TableName,
	i.name AS IndexName,
	icc.name AS IndexColumnName,
	ic.index_column_id AS IndexColumnId,	-- wichtig für Reihenfolge
	ic.is_descending_key AS IsDescending	-- Spaltensortierung
FROM sys.indexes AS i 
INNER JOIN sys.tables AS t ON i.object_id = t.object_id
INNER JOIN sys.index_columns AS ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id
INNER JOIN sys.columns AS icc ON ic.object_id = icc.object_id and ic.column_id = icc.column_id 
WHERE i.is_primary_key = 0	-- ohne Primärschlüssel
	-- AND i.type_desc IN ('HEAP', 'CLUSTERED', 'NONCLUSTERED') -- ohne XML, ...
ORDER BY SchemaName, Tablename, IndexName, IndexColumnId

Die Abfrage liefert bei Indizes mit mehreren Spalten entsprechend viele Zeilen; weitere Spalten könnte man bei Bedarf dazu nehmen.

Hinweis auch für andere Abfragen:
Outer Joins sollte man nur verwenden, wenn es nötig ist, da sie allgemein ineffizienter sind.

Gruß Elmar