编程获取表的外键约束
编程获取表的外键约束
提问于 2009-06-02 02:54:15
我试图使用DataTable来获取Server的架构。
但是，当试图检测ForeignKeys时，约束集合只会带来唯一的约束。
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如何获得ForeignKey约束？
回答 6
Stack Overflow用户
回答已采纳
发布于 2009-06-02 04:24:55
最后，我使用了对模式的直接查询。感觉不对，但完成了以下工作：
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这个问题的信息：Query to get all foreign key constraints in SQL Server 2000
Stack Overflow用户
发布于 2009-06-02 02:56:18
那真是出乎意料。最终，FillSchema方法不会返回外键信息。我有一些看起来应该这样做的示例代码，但它就是做不到。
如果您真正想要的是查询DB中所有FKs的编程方法，那么从代码中尝试这个系统存储过程。
msdb.dbo.sp_Help 'tableName‘
它返回一个数据集。第七个表包含表的所有约束，包括FKs。
Stack Overflow用户
发布于 2009-06-02 04:09:54
Hmm，如果禁用了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页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/937732
复制相关文章
相似问题
腾讯云开发者
