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.

Answer 2

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

Answer 3

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

Answer 4

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

Answer 5

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: http://stackoverflow.com/questions/831589/query-to-get-all-foreign-key-constraints-in-sqlserver-2000