SQLTeam.com | Weblogs | Forums

Databasetable with indexes (SQL Server 2000)


how can I find out in SQL Server 2000 on which tables indezes are and on which coloums?

EXEC sp_helpindex 'MyTable';

That will output all the index details for MyTable, substitute your table name(s) as needed.

SELECT Object_Name(i.id), i.name IndexName, sc.name KeyColumnName
FROM dbo.sysindexes i
JOIN dbo.syscolumns sc  ON i.id = sc.id
JOIN dbo.sysindexkeys ik ON ik.id = i.id AND ik.colid = sc.colid
ORDER BY  Object_Name(i.id), i.name, ik.keyno