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?

Answer 1

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.