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);
}
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.