Search code examples
.netsql-serversqlconnection

How To Get Foreign Key Information In .NET


I'm trying to use the .NET GetSchema method to retrieve metadata about some tables in SQL Server.
I can get back Columns information, like data type and size, but when I try "ForeignKeys" it doesn't tell me the name of the related table or the related column name.
Below is my current code.
Can this be done with GetSchema? If not, I'll resort to using SQL queries on the system tables or information tables, whatever they are called.
If you don't know the GetSchema answer feel free to suggest a good SQL query.

using (SqlConnection connection = new SqlConnection(connectionString))
{
    connection.Open();
    string[] restrictions = new string[4];
    restrictions[1] = schemaName;
    restrictions[2] = tableName;
    DataTable schema = connection.GetSchema("ForeignKeys", restrictions);
}

Solution

  • I think I'm going to use SMO objects if I can get them to run on my web host. I'm getting a new error that SqlClrProvider can't be found. I can't find that dll on my local machine, but SMO works on my local machine. Edit: I found the SqlClrProvider.dll, added the reference, re-published to my web host and it ran perfectly. Problem solved using SMO objects to get the foreign key metadata.