Examining a database schema from code (c#)

Hi all,

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.

CJ
LVL 3
cjmosAsked:
Who is Participating?
 
anarki_jimbelConnect With a Mentor Commented:
Funny but DBConnection class GetSchema method does not provide info you want.
Still this is possible. have a look:

http://blog.rioterdecker.net/blogs/valhalla/archive/2006/01/13/4.aspx
0
 
surajgupthaConnect With a Mentor Commented:
There are some system tables in SQL server that contain all this information. You can write queries to get the information about the columns, constraints, etc
Check out the following system tables
Information_Schema.Tables, Information_Schema.Columns, Information_Schema.Constraints
0
 
cjmosAuthor Commented:
Thanks both for your solutions and my apologies for keeping this question open for so long. I actually managed to solve the problem a while ago by using the system tables (as mentioned by surajguptha) . I'm spliting the points as anarki_jimbel answered with a viable solution and because I kept the question open for so long.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.