How to get columns Primary key constraints using SqlConnection.GetSchema()

Question

I have some code of ADO.NET to dynamically detect the database schema, what I need is how to get unique columns constraints and Primary key constraints using the GetSchema method on SqlConnection. This is the code that I have:

conn.Open();     
SqlCommand mSqlCommand = new SqlCommand("sp_pkeys", conn);
mSqlCommand.CommandType = CommandType.StoredProcedure;
mSqlCommand.Parameters.Add(
    "@table_name", SqlDbType.NVarChar).Value = tableName;
SqlDataReader mReader = mSqlCommand.ExecuteReader(
    (CommandBehavior.KeyInfo | CommandBehavior.SchemaOnly));
//ExecuteReader();
DataTable schema = mReader.GetSchemaTable();
mReader.Close();
conn.Close();

Answer 1

There is nothing in the call to GetSchemaTable on SqlConnection which will allow you to figure this out.

It might seem that you can, using the IsKey column value, which should return true for anything that contributes to uniquely identifying the record in the table. However, from the documentation for the IsKey column (emphasis mine):

true : The column is one of a set of columns in the rowset that, taken together, uniquely identify the row. The set of columns with IsKey set to true must uniquely identify a row in the rowset. There is no requirement that this set of columns is a minimal set of columns. This set of columns may be generated from a base table primary key, a unique constraint or a unique index.

Because of this, you can't guarantee that it contributes to a primary key per-se.

Now, if all you need is something to uniquely identify the row, then IsKey is fine, as the primary key is not always the way to uniquely identify a row (e.g. you can have natural identifiers with a unique index). Even if you have a primary key and a unique index with other columns, the values across all those columns in combination will always be unique.

However, if you specifically need to look at the columns that make up the primary key, then GetSchemaTable will not give you the information you need. Rather, you can just make a call to the sp_pkeys system stored procedure to find the names of the columns that contribute to making the primary key.