none
using SqlClient.SQLConnection GetSchema("ForeignKeys") RRS feed

  • Question

  • Hi Guys

    I am trying to write a small application for my use where i am extracting the User Tables and for each table getting the child tables (by foreign key relation). This is for both Oracle 10g and SqlServer 2005.

    For Oracle i retreived a list of Tables using GetSchema("Tables"), the foreign keys using GetSchema("ForeignKeys") and foreignkey columns using GetSchema("ForeignKeyColumns"). For each table i need i can fetch the foreign keys for that table using the "ForeignKeys" collection and the child table/column using the "ForeignKeyColumns" collection (using the CONSTRAINT_NAME).

    However Seems like a similar approach for SqlServer is not possible because the collection "ForeignKeys" does not give me the parent table (gives the foreign key name and the child table). There seems no other collection which

    Any clues as to how i could get the information i need ? Something similar to the approach taken for Oracle.

    Thanks in advance

    Regards

    Siddharth.
    Wednesday, February 4, 2009 11:02 PM

All replies

  • I believe using ForeignKeys in GetSchema should return that information for you, so your approach is correct. Which version of SQL Server are you connecting to?
    Val Mazur (MVP) http://www.xporttools.net
    Thursday, February 5, 2009 11:21 AM
    Moderator
  • Actually I have the same problem...

    And what is amazing : it is working with ADODB...But I need to do that without ADODB...

     

    does not work : (oConnection is a System.Data.Common.DBConnection)

                    DataTable oTableForeign = oConnection.GetSchema("ForeignKeys", new string[] { csCatalog, csSchema, csTable, null });

    it says that the collection ForeignKeys does not exist...

     

    this is working : (ado is an ADODB.Connection)


                    ADODB.Recordset oRec = ado.OpenSchema(ADODB.SchemaEnum.adSchemaForeignKeys, new object[] { csCatalog, csSchema, csTable, null }, System.Reflection.Missing.Value);

    Monday, March 22, 2010 4:25 PM