October 10, 2007 at 2:51 pm
How can I pull a report from a Database in the below format
table1 index1, index2 clustered or nonclustered
table 2 index1, index2 clustered or nonclustered
October 10, 2007 at 3:16 pm
I could only get part of it, dont know where to find the actual index names
SELECT DISTINCT
= OBJECT_NAME(OBJECT_ID), 'CLUSTERED'
FROM SYS.INDEXES
WHERE [TYPE]=1 and OBJECTPROPERTY(OBJECT_ID,'IsUserTable') = 1
UNION
SELECT DISTINCT
= OBJECT_NAME(OBJECT_ID),'NONCLUSTERED'
FROM SYS.INDEXES
WHERE [TYPE]=2 and OBJECTPROPERTY(OBJECT_ID,'IsUserTable') = 1
ORDER BY
type 1 is CLUSTERED, 2 is NONCLUSTERED and 0 would mean there is no index on the table.
somehow you should be able to join in the table 'sysindexes' (no dot) to get the names
October 10, 2007 at 3:38 pm
Thank you very much
October 10, 2007 at 3:40 pm
I need to get the column name on which these indexes are defined.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply