sys.index_columns Gives more than one row for PK Index

Question

Post reply

sys.index_columns Gives more than one row for PK Index

dec_obrien

Default port

Points: 1486
More actions
February 8, 2012 at 8:05 am

#255285

Hi,
I'm trying to put together a script that describes my indexes but am running into an issue where there seem to be extra rows added to the sys.index_columns view. Where I put a PK on ONE column in a table, it lists the Index twice in sys.index_columns. Why is this or am I missing something?
Here's what I'm using:
select SysI.object_id
,SysI.name As IndexName
,SysI.type_desc AS IndexType
,SysC.name AS ColName
,CASE SysI.is_unique
WHEN 0 THEN 'YES'
WHEN 1 THEN 'NO'
END AS AllowDuplicateValues
,CASE SysI.is_primary_key
WHEN 0 THEN ''
WHEN 1 THEN 'YES'
END AS PrimaryKey
fromsys.indexes SYSI INNER JOIN
sys.tables SYST ON sysi.object_id = syst.object_id INNER JOIN
sys.columns SysC ON SysC.object_id=SysT.object_id INNER JOIN
sys.index_columns SysIC ON SysIC.object_id=SysC.object_id AND SysIC.column_id=SysC.column_id

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

dec_obrien Default port Points: 1486 More actions · Answer 1

Sorry,

Solved. I forgot to add the join between sys.index_coluns.index_id and sys.index_indexes.index_id

Fixed:

SELECTSysI.object_id

,SysI.name AS IndexName

,SysI.index_id

,SysI.type_desc AS IndexType

,SysC.name AS ColName

,CASE SysI.is_unique

WHEN 0 THEN 'YES'

WHEN 1 THEN 'NO'

END AS AllowDuplicateValues

,CASE SysI.is_primary_key

WHEN 0 THEN '' WHEN 1 THEN 'YES'

END AS PrimaryKey

FROM sys.indexes AS SysI INNER JOIN

sys.tables AS SysT ON SysI.object_id = SysT.object_id INNER JOIN

sys.columns AS SysC ON SysC.object_id = SysT.object_id INNER JOIN

sys.index_columns AS SysIC ON SysIC.object_id = SysC.object_id AND SysIC.column_id = SysC.column_id AND SysI.index_id = SysIC.index_id

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

I ran that on a testing database and saw exactly what I would expect.

Can you post the output that's confusing you?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

dec_obrien Default port Points: 1486 More actions · Answer 3

Yes, I found the problem, I had missed one of the Joins as noted in my second post.

Sorry!