Getting the Foreign Key constraints of a table programatically

Question

I'm trying to use a DataTable to get the schema of a SQL Server DB.
But, when try to detect the ForeignKeys, the constraints collection only brings the UNIQUE constraints.

Private Sub ShowConstraints(ByVal tableName As String)

    Dim table As DataTable = New DataTable(tableName)
    Using connection As SqlConnection = New SqlConnection(GetConnectionString)

        Dim adapter As SqlDataAdapter = New SqlDataAdapter("Select top 1 * from " + _                  
                                                  tableName, connection)
        connection.Open()
        adapter.FillSchema(table, SchemaType.Mapped)

        For Each c As Constraint In table.Constraints
            If TypeOf c Is ForeignKeyConstraint Then
                Dim fk As ForeignKeyConstraint = CType(c, ForeignKeyConstraint)
                Console.WriteLine("** FK ** relatedTable: {0}; RelatedColumns: {1}", _
                    fk.RelatedTable, fk.RelatedColumns)
            Else
                Console.WriteLine("** Whatever ** Name: {0}; Type: {1}", _
                                           c.ConstraintName, c.GetType.ToString)
            End If
        Next

    End Using


End Sub

How can I get the ForeignKey Constraints?

Scott Ferguson · Answer 1 · 2009-06-02T03:46:24.397

4

Well, that was unexpected. It turns out that Foreign Key information is not returned by the FillSchema method after all. I had some sample code that looked like it should do it, but it just doesn't.

If what you really want is a programmatic way to query all the FKs in a DB, then try this system stored-procedure from your code.

msdb.dbo.sp_Help 'tableName'

it Returns a Dataset. The 7th table has all the constraints of your table, including the FKs.

edited Jun 02 '09 at 03:46

answered Jun 02 '09 at 02:56

Scott Ferguson

7,690
7
41
64

Thanks for your help. I ended up querying the DB. It must be a bug, don't you think? – Eduardo Molteni Jun 02 '09 at 04:27
Not sure if it's a bug, but it certainly isn't intuitive! You would definitely expect that the FillSchema() method would return ForeignKeyConstraints, since DataTable has a Constraints Collection. I can only presume they aren't returned for performance reasons.. – Scott Ferguson Jun 02 '09 at 05:22
In MSDN names the FKs as objects returned in the collection... http://msdn.microsoft.com/en-us/library/system.data.datatable.constraints.aspx – Eduardo Molteni Jun 02 '09 at 11:25

score 3 · Accepted Answer · edited May 23 '17 at 10:29

I ended up using a direct query to the schema. Does not feel right, but gets the work done:

Private Sub ShowFKs()
    Dim sqlstmt As New StringBuilder

    sqlstmt.Append(" SELECT ")
    sqlstmt.Append("    rc.CONSTRAINT_NAME,         ")
    sqlstmt.Append("    rcu.TABLE_NAME 'Referencing Table', ")
    sqlstmt.Append("    rcu.COLUMN_NAME 'Referencing Column',")
    sqlstmt.Append("    rcu1.TABLE_NAME 'Referenced Table',")
    sqlstmt.Append("    rcu1.COLUMN_NAME 'Referenced Column'")
    sqlstmt.Append(" FROM")
    sqlstmt.Append("    INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc")
    sqlstmt.Append(" INNER JOIN ")
    sqlstmt.Append("    INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE rcu ")
    sqlstmt.Append("      ON rc.CONSTRAINT_CATALOG = rcu.CONSTRAINT_CATALOG ")
    sqlstmt.Append("         AND rc.CONSTRAINT_NAME = rcu.CONSTRAINT_NAME")
    sqlstmt.Append(" INNER JOIN ")
    sqlstmt.Append("    INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE rcu1 ")
    sqlstmt.Append("      ON rc.UNIQUE_CONSTRAINT_CATALOG = rcu1.CONSTRAINT_CATALOG ")
    sqlstmt.Append("         AND rc.UNIQUE_CONSTRAINT_NAME = rcu1.CONSTRAINT_NAME")

    Using connection As SqlConnection = New SqlConnection(GetConnectionString)
        Dim cmd As New SqlCommand(sqlstmt.ToString, connection)
        Dim reader As SqlDataReader

        cmd.CommandType = CommandType.Text

        connection.Open()
        reader = cmd.ExecuteReader(CommandBehavior.CloseConnection)

        Do While reader.Read
            For i As Integer = 0 To reader.FieldCount - 1
                Console.WriteLine("** {0} = {1}", reader.GetName(i), reader.GetValue(i).ToString)
            Next
            Console.WriteLine("---------------------")
        Loop
    End Using

End Sub

Info from this question: Query to get all foreign key constraints in SQL Server 2000

score 1 · Answer 3 · answered Jun 02 '09 at 04:09

Hmm, the ForeignKeyConstraint objects may not be filled if the DataRelations are disabled or else are not either enforcing or cascading the constraint.

This might work, however:

    Private Sub ShowConstraints(ByVal tableName As String)

    Dim table As DataTable = New DataTable(tableName)
    Using connection As SqlConnection = New SqlConnection(GetConnectionString)

        Dim adapter As SqlDataAdapter = New SqlDataAdapter("Select top 1 * from " + _
                                                  tableName, connection)
        connection.Open()
        adapter.FillSchema(table, SchemaType.Mapped)

        Console.WriteLine(" ** Parent Relations ** ")
        For Each dr As DataRelation In table.ParentRelations
            Console.Write("name: {0}: ", dr.RelationName)
            Dim fk As ForeignKeyConstraint = dr.ChildKeyConstraint
            If Not (fk Is Nothing) Then
                Console.WriteLine(" RelatedTable {0}; RelatedColums {1}", _
                    fk.RelatedTable, fk.RelatedColumns)
            Else
                Console.WriteLine(" no constraint.")
            End If
        Next

        Console.WriteLine(" ** child Relations ** ")
        For Each dr As DataRelation In table.ChildRelations
            Console.Write("name: {0}: ", dr.RelationName)
            Dim fk As ForeignKeyConstraint = dr.ChildKeyConstraint
            If Not (fk Is Nothing) Then
                Console.WriteLine(" RelatedTable {0}; RelatedColums {1}", _
                    fk.RelatedTable, fk.RelatedColumns)
            Else
                Console.WriteLine(" no constraint.")
            End If
        Next
    End Using

End Sub

In a related note, still don't know what kind of info was supposed to bring this "relations" — Eduardo Molteni, Jun 02 '09 at 04:29

score 1 · Answer 4 · answered Jun 02 '09 at 04:10

1

The best way is to query the information_schema tables directly:

    SELECT 
      cu.TABLE_SCHEMA + '.' + cu.TABLE_NAME AS TABLE_NAME 
    , cu.COLUMN_NAME 
    , rc.CONSTRAINT_NAME AS FK_NAME 
    , rc.UNIQUE_CONSTRAINT_NAME AS REFERENCE 
   FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE cu INNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc 
       ON cu.CONSTRAINT_NAME = rc.CONSTRAINT_NAME

answered Jun 02 '09 at 04:10

JAG

1,751
2
16
21

Is this Oracle? I believe the question relates to SQL Server. – Scott Ferguson Jun 02 '09 at 04:20
Yes, it is SQL server. But I was hopping to use ADO to go multi-vendor (kidding, it is for an internal project) – Eduardo Molteni Jun 02 '09 at 04:31

score 0 · Answer 5 · answered Jul 02 '14 at 07:58

This query worked for me.

SELECT rc.[CONSTRAINT_NAME]
  ,fk.[TABLE_NAME]
  ,fk.[COLUMN_NAME]
  ,pk.[TABLE_NAME]
  ,pk.[COLUMN_NAME]
 FROM [LocalV4].[INFORMATION_SCHEMA].[REFERENTIAL_CONSTRAINTS] rc
 inner join [LocalV4].[INFORMATION_SCHEMA].[CONSTRAINT_COLUMN_USAGE] fk on fk.[CONSTRAINT_NAME] = rc.[CONSTRAINT_NAME]
 inner join [LocalV4].[INFORMATION_SCHEMA].[CONSTRAINT_COLUMN_USAGE] pk on pk.[CONSTRAINT_NAME] = rc.[UNIQUE_CONSTRAINT_NAME]

score 0 · Answer 6 · answered Jun 02 '09 at 03:52

0

Query the information_schema tables (user_tables / all_tables for oracle) - they contain the metadata about your database.

answered Jun 02 '09 at 03:52

Brendan Green

11
2

I was trying to avoid that, since there is already a GetSchema method in ADO – Eduardo Molteni Jun 02 '09 at 04:28

Getting the Foreign Key constraints of a table programatically

6 Answers6

Linked