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'