Giovanni Montrone's Blog

Dynamic determination of primary key in SQL Server

Saturday, May 10 2008, Home, gio

One of the recent projects I've been working on forced me to do the following:
Delete from tableName where its primary key=X. 

The tricky part is this: We don't know the column_name of the primary key.  The reason for this is because the table_name may be different each time i run the statement;  The sql statement is completely dynamically built.

The table name is determined by grabbing it from a table that stores table names (along with other info)

So now, I have the table name, but need to get the primary key's name.

I found two ways to do this:

SELECT col.name FROM sysindexes indx
    INNER JOIN sysobjects sobj ON indx.id = sobj.id
    INNER JOIN sysobjects pk ON indx.name = pk.name AND pk.parent_obj = indx.id AND pk.xtype = 'PK'
    INNER JOIN sysindexkeys ik on indx.id = ik.id AND indx.indid = ik.indid
    INNER JOIN syscolumns col ON ik.id = col.id AND ik.colid = col.colid
WHERE 
    sobj.name = 'table_name'

After talking to a co-worker, he mentioned that there is an Information_Schema View which can give you similar information but is a little easier to use.

SELECT ky.COLUMN_NAME FROM information_schema.table_constraints tbl 
    INNER JOIN information_schema.key_column_usage ky 
ON tbl.constraint_name = ky.constraint_name
    WHERE tbl.constraint_type = 'PRIMARY KEY' AND tbl.table_name = 'table_name'

So now once I have this, I can create my sql statement (C# psuedo):

string x = "DELETE FROM " + tableName + " WHERE " + primaryKeyColumn + " = " + value.ToString()

In my scenario all of my primary keys are ints, so this will work, if not we'd have to do a few extra things...