Pachydermitis (de******@hotmail.com) writes:
I have been able to get the TableName and IndexNames (along with a few
I don't want _WA_. . .) but I can't seem to get the column names or
get rid of the _WA_ ones.
Had you used the query I suggested, you would have been relieved from the
_WA "indexes". (Which are statistics and hypothetical indexes.)
I was trying to get TableName, IndexName, ColumnName
Here is a query that gives this. For multi-column indexes you get one
row per index. If you want all columns for an index on one line, you
will have run some iteration.
SELECT "table" = object_name(i.id), i.name,
isclustered = indexproperty(i.id, i.name, 'IsClustered'),
"column" = col_name(i.id, ik.colid), ik.keyno
FROM sysindexes i
JOIN sysindexkeys ik ON i.id = ik.id
AND i.indid = ik.indid
WHERE i.indid BETWEEN 1 AND 254
AND indexproperty(i.id, name, 'IsHypothetical') = 0
AND indexproperty(i.id, name, 'IsStatistics') = 0
AND indexproperty(i.id, name, 'IsAutoStatistics') = 0
AND objectproperty(i.id, 'IsMsShipped') = 0
ORDER BY "table", "isclustered" DESC, i.name, ik.keyno
--
Erland Sommarskog, SQL Server MVP,
so****@algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp