Examining a database schema from code (c#)

Posted on 2007-07-24
Medium Priority
Last Modified: 2008-02-01
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.

Question by:cjmos
LVL 30

Accepted Solution

anarki_jimbel earned 1000 total points
ID: 19561154
Funny but DBConnection class GetSchema method does not provide info you want.
Still this is possible. have a look:

LVL 21

Assisted Solution

surajguptha earned 1000 total points
ID: 20226345
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

Author Comment

ID: 20226462
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.

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
The article shows the basic steps of integrating an HTML theme template into an ASP.NET MVC project
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…
Loops Section Overview

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question