I'm writing an application in c# using an Sql 2005 database. The application needs to be able to examine the relationships and column constraints going on in the database and make decisions on based on them. What is the best way to go about doing this?
I know I can query the system schema tables but have so far only managed to find out very limited information about the various relationship between the tables and columns (like the column that has the constraint on it, the type (PK, FK, UC) of the constraint and it's name). Ideally I need to be able to do the following:
For any given table find all the columns that have constraints on them. If they are PK or FK constraints, find out info on the table and column(s) on the other side of the constraint. Is this possible.
Any help is greatly appreciated.