How to retrieve Primary Key metadata in ADO.NET 4?

I'm trying to retrieve the Primary Key metadata for all tables in a database using ADO.NET 4.

The GetSchema() methods let you pull down a lot of metadata, including Indexes and Foreign Keys, but there's nothing there to identify Primary Keys. It seems like such a glaring omission to me that I figure it must be me who's missing something!

Fr开发者_JAVA技巧om what I've been able to find, there seems to be two 'workarounds' (for SQL Server at least):

• use SqlDataReader ExecuteReader() KeyInfo option; or
• use SqlDataAdapter.Fill( dataTable ) method and then use KeyInfo property on the returned dataTable.

Can anyone please tell me if there's a clean standard way to identify all Primary Keys in a database and retrieve their metadata that will work across all Data Provider implementations (incl Oracle, DB2, MySql)?

You can retrieve the primary key from the information_schema.table_constraints:

select c.constraint_name, c.table_name, k.column_name
from   information_schema.table_constraints as c
join   information_schema.key_column_usage as k
on     c.constraint_catalog = k.constraint_catalog 
       and c.constraint_schema = k.constraint_schema 
       and c.constraint_name = k.constraint_name 
where  c.constraint_type = 'primary key'
       and c.table_name = 'YourTable'

MSDN Page on GetSchema and the collection PrimaryKeys. However it does not work with SQL Server, as it says at the top

_{Some schema collections are incomplete, such as SQL Server, which does not contain a collection for primary keys.}

In fact, the reading of it implies that GetSchema is not reliable as a means of cross-db schema retrieval.

• MSDN: The following code listing shows how to use the custom SqlClientSchema.xml file to override the default GetSchema operations and return a PrimaryKeys collection.

The other option which is supported by most major RDBMS is to query information_schema for all the primary keys.

select *
from information_schema.TABLE_CONSTRAINTS
where CONSTRAINT_TYPE = 'PRIMARY KEY'

You can join this to KEY_COLUMN_USAGE for the columns by position order

select PK.TABLE_SCHEMA, PK.TABLE_NAME, C.ORDINAL_POSITION, C.COLUMN_NAME
from information_schema.TABLE_CONSTRAINTS PK
inner join information_schema.KEY_COLUMN_USAGE C
    on C.TABLE_SCHEMA = PK.TABLE_SCHEMA
    and C.TABLE_CATALOG = PK.TABLE_CATALOG
    and C.TABLE_NAME = PK.TABLE_NAME
where PK.CONSTRAINT_TYPE = 'PRIMARY KEY'

Take note that even though information_schema has good support, it can be different in small ways. In SQL Server, table_catalog (=db name) is always limited to the current db, whereas in MySQL, it will return tables from any catalog (db), so you may need a catalog filter to be accurate.

Is there a unified API to get all the metadata (including Primary Key information) for all backends - answer is 'No'

Ideally the DbConnection.GetSchema overrides for each backend should provide various collections to get all possible metadata, but sadly that is not the case. I usually use the following approaches in that order

1. GetSchema method with appropriate schema collection
2. System stored procedures,if available. (sys.sp_primarykeys etc)
3. Query system views (INFORMATION_SCHEMA.KEY_COLUMN_USAGE etc)
4. Query the system view(s) with a custom query if all information isn't available in a single view

For Sql Server 2005 and above I've used the system stored procedure - sys.sp_primary_keys_rowset2

If required I'll provide the information for other backends also.

There's a library, Kailua - The forgotten methods in the ADO.NET API. , that does provide this and additional metadata for the top 5 vendors. This info is vendor specific.