How to validate if the table has the primary key?

In our last Sprint, when I tested in Staging Environment, some basic functions were broken. Per conversation with our developers, they said this table doesn’t have a primary key due to Database Change and ADO.NET Entity Framework needs a primary key in the table.  That’s why it is broken.  Nice Catch!

It made me think of adding some unit tests of validating if each table has the primary key. If I can run those tests after we queue a new TFS build, I will know if it is a good build or not.

How do I validate if each table has the primary key? After doing some research, I can use the following SQL query in my resource file and use C# to run the test.  Everything is working perfectly.

SELECT K.TABLE_NAME, K.COLUMN_NAME, K.CONSTRAINT_NAME

FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS C JOIN

INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS K

ON C.TABLE_NAME = K.TABLE_NAME AND 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 K.TABLE_NAME = 'table name'