Automatically using table foreign key constraints

Question

I am trying to use a DataTable to retrieve a SQL Server database schema.
But when trying to discover ForeignKeys, the collection of constraints contains only 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 ExternalKey restrictions?

Answer 1

I ended up using a direct schema request. Doesn't feel good, but does the job:

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

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

Answer 2

, . , FillSchema. , , , .

FK , .

msdb.dbo.sp_Help 'tableName'

it . 7- , FK.

Answer 3

, ForeignKeyConstraint , DataRelations .

, :

    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

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

Answer 5

The best way is to directly query the information_schema tables:

    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 6

This request 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]