如何查找特定数据库表的外键。 [英] How to find foreign keys for a specific database table.

问题描述

大家好：

我正在对我三年前写的应用程序进行一些修改。该应用程序旨在采用Microsoft Access或SQL Server数据库。列出其中包含
的表，并动态创建所选表的数据输入表。

我想添加的修改是一个下拉框，可以更轻松地输入国家和/或州名等数据。如果您有一个已经设计用于数据输入的表单，这很容易完成。在运行时动态创建表单时，不是很多
。

我的问题是：如何找到并返回外键表的约束？

让我举个例子。我在四个表之间有以下关系。

客户的数据输入表格如下：

如您所见，客户数据输入表的外键是;分别是CountryID和StateID。通常，我会查询MSys表
  以返回tblCustomers的外键约束。但是，.NET框架的结构化方式阻止了这一点，但.NET框架提供了允许设计人员完成此任务的方法。

所以有人可以指出我如何输入查询的方式：SELECT * FROM [tblConstraints] WHERE [strTbl]。

它返回类似于：

对于tblCustomers，外键为：tblCountries.CountryID和tblStates.StateID。

谢谢，

---

MRM256

解决方案

对于SQL-Server你有两种方法，使用T-SQL或。

Hi Everyone:

I am making some modifications to an application I wrote about three years ago. The application is designed to take either a Microsoft Access or SQL Server database. List the tables contained therein, and dynamically create a data entry form for the selected table.

The modification I wish to add is a pull down box to make entering data like a country and/or state name easier. This is easily done if you have a form already designed for data entry. Not so much when you are creating a form dynamically at runtime.

My question is: How do I find and return the Foreign Key constraints for a table?

Let me give an example. I have the following relationships between four tables.

The customer's data entry form is like this:

As you can see the foreign keys for the Customer Data Entry form are; CountryID and StateID respectively. Normally, I would query the MSys tables  to return the foreign key restraints for tblCustomers. However, the structured manner of the .NET framework prevents this, but the .NET framework has methods available that will allow the designer to accomplish this.

So can someone point me in the direction on how I can enter a query something like: SELECT * FROM [tblConstraints] WHERE [strTbl].

And it returns something like:

For tblCustomers the foreign keys are: tblCountries.CountryID and tblStates.StateID.

Thanks,

---

MRM256

解决方案

For SQL-Server you have two methods, using T-SQL or SMO.

For SMO checkout my GitHub repo for getting started with SMO. In the class ColumnDetails you will see

<Category("Items"), Description("ForeignKeys DataTable")>
Public Property ForeignKeys() As DataTable
<Category("Items"), Description("ForeignKeys break down")>
Public Property ForeignKeysList() As List(Of ForeignKeysDetails)
Public Overrides Function ToString() As String
    Return Name
End Function

Which is hydrated in TableInformation class e.g.

T-SQL

DECLARE @TableName AS NVARCHAR(50) = 'Report'
SELECT 
   OBJECT_NAME(f.parent_object_id) TableName,
   COL_NAME(fc.parent_object_id,fc.parent_column_id) ColumnName
FROM 
   sys.foreign_keys AS f
INNER JOIN 
   sys.foreign_key_columns AS fc 
      ON f.OBJECT_ID = fc.constraint_object_id
INNER JOIN 
   sys.tables t 
      ON t.OBJECT_ID = fc.referenced_object_id
WHERE 
   OBJECT_NAME (f.referenced_object_id) = @TableName

For MS-Access look at the following thread.

这篇关于如何查找特定数据库表的外键。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！