Query to fetch MsSQL table unique index and primary keys

Question

SELECT sysobjects.xtype, syscolumns.name, sysindexkeys.indid, sysobjects.type
FROM
    syscolumns
LEFT JOIN sysobjects ON syscolumns.id = sysobjects.id
LEFT JOIN sysindexkeys ON (
     syscolumns.id = sysindexkeys.id AND syscolumns.colid = sysindexkeys.colid
)
WHERE sysobjects.name = '{$table}'
AND sysindexkeys.indid IS NOT NULL
ORDER BY sysindexkeys.indid, sysindexkeys.keyno

I'm actually using the following query in order to retreive the column name and the keyno.

The problem is my table has 3 fields:

user_id
config_name
config_value

With a primary key on user_id AND config_name.

I would expect to get the collection:

[
  ['name' => 'user_id', 'keyno' => 1],
  ['name' => 'config_name', 'keyno' => 1]
]

But i get :

[
  ['name' => 'user_id', 'keyno' => 1],
  ['name' => 'config_name', 'keyno' => 2]
]

What am I doing wrong?

edit: I get the same weirdos results using a two index table

table: project_image_id project_id project_image_src

PK on project_image_id AND unique index on project_id AND project_image_src

Expected:

[
  ['name' => 'project_image_id', 'keyno' => 1],
  ['name' => 'project_id', 'keyno' => 2]
  ['name' => 'project_image_src', 'keyno' => 2]
]

But i get :

[
  ['name' => 'project_image_id', 'keyno' => 1],
  ['name' => 'project_id', 'keyno' => 1]
  ['name' => 'project_image_src', 'keyno' => 2]
]

Answer 1

That seems correct to me, your index has 2 columns, user_id has position 1 and config_name has position 2

From BOL keyno: Position of the column in the index

why do you think they would both be 1?

Here you go, you needed to join back to the sysobject table but on the index not the table itself

SELECT s2.xtype, syscolumns.name, sysindexkeys.indid, sysobjects.type
FROM
    syscolumns
LEFT JOIN sysobjects ON syscolumns.id = sysobjects.id
LEFT JOIN sysindexkeys ON (
     syscolumns.id = sysindexkeys.id AND syscolumns.colid = sysindexkeys.colid
)
join sysobjects s2 on s2.parent_obj = sysindexkeys.id
WHERE sysobjects.name = '{$table}'
and s2.type = 'K'
AND sysindexkeys.indid IS NOT NULL
ORDER BY sysindexkeys.indid, sysindexkeys.keyno

Answer 2

The problem is that you get the "Position of the column in the index". In that way, it does not exists two columns with the same position.

Answer 3

SELECT sysobjects.name , syscolumns.name, systypes.name, syscolumns.length , syscolumns.xprec , syscolumns.xscale ,

( SELECT 'PK' FROM syscolumns C WHERE C.name = syscolumns.name AND C.id IN (SELECT O.id FROM sysobjects O WHERE O.name = sysobjects.name) AND C.colid IN (SELECT SIK.colid FROM sysindexkeys SIK JOIN sysobjects SO ON SIK.id= SO.id
WHERE SIK.indid = 1 AND SO.[name] = sysobjects.name) ) FROM sysobjects , syscolumns , systypes
WHERE sysobjects.xtype='U' AND sysobjects.id = syscolumns.id AND syscolumns.xtype=systypes.xtype AND sysobjects.name LIKE ? ORDER BY sysobjects.name , syscolumns.COLORDER

BY FARIS ZURIEKAT

Answer 4

There are a couple decent Primary Key solutions, but they do not completely answer the request. It was also asked how to determine if a column is a Unique Index.

Option 1:

To determine a unique index, the following system function works to tell you the status of a specific index, "IndexName," in table "TableName":

SELECT INDEXPROPERTY(OBJECT_ID('TableName'),'IndexName','IsUnique')

Option 2:

However, the question above stems from a desire to know if a COLUMN is a unique index. This means that you need a list of all indexes for which the column in question is the only column in the index. Therefore, a combination like this can be used:

-- Note: If a "1" appears in the select below, the index (and by extension, the column) is a unique index.


SELECT si.name, INDEXPROPERTY(x.TableID,si.name,'IsUnique') [IsUnique]
FROM sysindexes si
JOIN (SELECT OBJECT_ID('TableName') [TableID]) x  -- HERE IS YOUR TABLE NAME
  ON si.id = x.TableID
-- This restricts the selection to your column.
JOIN (
    SELECT id, indid 
    FROM sysindexkeys
    WHERE colid IN (
        -- For 2005 and up: SELECT COLUMNPROPERTY(OBJECT_ID('TableName'), 'ColumnName', 'ColumnId')
        SELECT colid
        FROM syscolumns
        WHERE name IN ('ColumnName')              -- HERE IS YOUR COLUMN NAME
    )
) y
  ON si.indid = y.indid
 AND x.TableID = y.id
WHERE si.status <> 0
  -- This eliminates occurrences of the column found in indexes with multiple columns.
  AND si.indid NOT IN (
    SELECT indid
    FROM sysindexkeys
    WHERE id = x.TableID
    GROUP BY indid
    HAVING COUNT(*) > 1
)

Option 3:

Create a table (IndexTable) with index_name, index_description, index_keys, and insert the results from the following command:

sp_helpindex 'TableName'

Then, query from this result (or use a similar query):

SELECT *
FROM IndexTable
WHERE index_description LIKE '%unique%'
  AND index_keys = 'ColumnName'

Hope this helps, or please post if you have a more concise solution.