February 8, 2012 at 8:05 am
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
from sys.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
February 8, 2012 at 8:09 am
Sorry,
Solved. I forgot to add the join between sys.index_coluns.index_id and sys.index_indexes.index_id
Fixed:
SELECT SysI.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
February 8, 2012 at 8:15 am
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
February 8, 2012 at 8:46 am
Yes, I found the problem, I had missed one of the Joins as noted in my second post.
Sorry!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply