Comma Separating

[SQLScholar]

Hey all,

I am trying to get a comma separated list of all my pk columns for a table.

So i have this

DECLARE @PKCols VARCHAR(100)

SELECT  @PKCols = CASE WHEN @PKCols IS NULL THEN ''
                       ELSE @PKCols + ','
                  END + c.name
FROM    sysindexes i
        INNER JOIN sysobjects t ON i.id = t.id
        INNER JOIN sysindexkeys k ON i.indid = k.indid
                                     AND i.id = k.ID
        INNER JOIN syscolumns c ON c.id = t.id
                                   AND c.colid = k.colid
WHERE   i.id = t.id
        AND i.indid BETWEEN 1 AND 254
        AND ( i.status & 2048 ) = 2048
        AND t.id = OBJECT_ID('tblMI_QuoteAdditionalDetailsUp')
ORDER BY t.name ,
        k.KeyNo

SELECT  @PKCols

Which returns this

QuoteId,LinkId

Now if i change it to add the table table (becuase i want to find the schema of the table) to this

DECLARE @PKCols VARCHAR(100)

SELECT  @PKCols = 
        CASE WHEN @PKCols IS NULL THEN ''
             ELSE @PKCols + ','
        END + c.name 
FROM    sysindexes i 

        INNER JOIN sysobjects t ON i.id = t.id
       
        INNER JOIN sysindexkeys k ON i.indid = k.indid
                                     AND i.id = k.ID
        INNER JOIN syscolumns c ON c.id = t.id
                                   AND c.colid = k.colid
        INNER JOIN sys.tables tabs ON i.id = tabs.object_id
WHERE   i.id = t.id
        AND i.indid BETWEEN 1 AND 254
        AND ( i.status & 2048 ) = 2048
        AND t.id = OBJECT_ID('tblMI_QuoteAdditionalDetailsUp')
ORDER BY t.name ,
        k.KeyNo

SELECT  @PKCols

I now get this

LinkId

So thought it must be something to do with the join. So i ran this:

SELECT   c.name 
FROM    sysindexes i 

        INNER JOIN sysobjects t ON i.id = t.id
       
        INNER JOIN sysindexkeys k ON i.indid = k.indid
                                     AND i.id = k.ID
        INNER JOIN syscolumns c ON c.id = t.id
                                   AND c.colid = k.colid
        INNER JOIN sys.tables tabs ON i.id = tabs.object_id
WHERE   i.id = t.id
        AND i.indid BETWEEN 1 AND 254
        AND ( i.status & 2048 ) = 2048
        AND t.id = OBJECT_ID('tblMI_QuoteAdditionalDetailsUp')
ORDER BY t.name ,
        k.KeyNo

And i still get this

QuoteId
LinkId

So what is going on here? I cant work out how i am missing a record on the second query.

Any help gratefully recieved!

Dan


----------------------------------------

http://www.fountain.me.uk

Be who you are and say what you feel, because those who mind don't matter and those who matter don't mind - Bernard Baruch

Computer Science is no more about computers than astronomy is about telescopes - EW Dijkstra
----------------------------------------

 

[gmmastros]

It looks like you are joining the indexid to the table's object_id

Change:
[tt]
INNER JOIN sys.tables tabs [!]ON i.id = tabs.object_id[/!]
[/tt]

To:

INNER JOIN sys.tables tabs ON [!]t[/!].id = tabs.object_id

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom