در این پست نحوه مشاهده همه جداول یک دیتابیس در SQL Server که دارای foreign keys هستند، را به شما دوستان نشان خواهیم داد که با چند روش اقدام به این کار خواهیم نمود. صرفنظر از این که یک جدول چند foreign key دارد، ولی فقط یکبار نام آن نمایش داده می شود. در واقع فقط نام جداولی که Foreign Keys دارند نمایش داده می شود.


همه مثال های زیر فقط در یک دیتابیس انجام می شود و بدیهی که همه Result ها یکسان خواهد بود.

OBJECTPROPERTY() with sys.tables

نخستین گزینه تابع OBJECTPROPERTY است که برای کوئری از View سیستمی sys.tables مورد استفاده قرار می گیرد(البته برای این خروجی ای که مد نظر داریم).
این تابع آرگومان TableHasForeignKey را دریافت می کند که 0 یا 1 خواهد بود(NULL هم می تواند باشد). اگر 1 بود، معنی آن وجود کلید خارجی در این جدول و 0 به معنی عدم وجود آن است. پس در این شرایط شما می توانید با استفاده از WHERE، خروجی را فیلتر کرده و فقط جداولی که دارای کلید خارجی هستند را نمایش دهید.

SELECT

  SCHEMA_NAME(schema_id) AS [Schema],

  name AS [Table]

FROM sys.tables

WHERE OBJECTPROPERTY(object_id, 'TableHasForeignKey') = 1

ORDER BY [Schema], [Table];

+----------+---------+

| Schema   | Table   |

|----------+---------|

| dbo      | Albums  |

| dbo      | Artists |

+----------+---------+

OBJECTPROPERTY() with INFORMATION_SCHEMA.TABLES

در این روش، با کوئری روی View ی سیستمی INFORMATION_SCHEMA.TABLES از تابع OBJECTPROPERTY استفاده می شود:

SELECT

  TABLE_SCHEMA,

  TABLE_NAME

FROM INFORMATION_SCHEMA.TABLES 
WHERE OBJECTPROPERTY(OBJECT_ID(CONCAT(TABLE_SCHEMA, '.', TABLE_NAME)),'TableHasForeignKey') = 1 AND

TABLE_TYPE='BASE TABLE'

ORDER BY TABLE_SCHEMA, TABLE_NAME;

+----------------+--------------+

| TABLE_SCHEMA   | TABLE_NAME   |

|----------------+--------------|

| dbo            | Albums       |

| dbo            | Artists      |

+----------------+--------------+

OBJECTPROPERTY() with sys.objects

در این روش هنگام کوئری روی View سیستمی sys.objects، از تابع OBJECTPROPERTY استفاده خواهد شد:

SELECT

  SCHEMA_NAME(schema_id) AS [Schema],

  name AS [Table]

FROM sys.objects

WHERE type = 'U'

AND OBJECTPROPERTY(OBJECT_ID(CONCAT(SCHEMA_NAME(schema_id), '.', name)), 'TableHasForeignKey') = 1

ORDER BY [Schema], [Table]

+----------+---------+

| Schema   | Table   |

|----------+---------|

| dbo      | Albums  |

| dbo      | Artists |

+----------+---------+

INFORMATION_SCHEMA.TABLE_CONSTRAINTS with DISTINCT

در این روش از View سیستمی INFORMATION_SCHEMA.TABLE_CONSTRAINTS که نوع constraint آن FOREIGN KEY است استفاده می شود. در این حالت از DISTINCT برای جلوگیری از بازگشت بیش از یک بار جداول هنگامی که بیش از یک کلید خارجی دارند ، استفاده می شود.

SELECT DISTINCT

  CONSTRAINT_SCHEMA,

  TABLE_NAME

FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE CONSTRAINT_TYPE = 'FOREIGN KEY';

+---------------------+--------------+

| CONSTRAINT_SCHEMA   | TABLE_NAME   |

|---------------------+--------------|

| dbo                 | Albums       |

| dbo                 | Artists      |

+---------------------+--------------+

در صورت حذف DISTINCT اتفاق زیر رخ می دهد:

SELECT

  CONSTRAINT_SCHEMA,

  TABLE_NAME

FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE = 'FOREIGN KEY';

+---------------------+--------------+

| CONSTRAINT_SCHEMA   | TABLE_NAME   |

|---------------------+--------------|

| dbo                 | Albums       |

| dbo                 | Albums       |

| dbo                 | Artists      |

+---------------------+--------------+

در این حالت همانطور که مشاهده کردید، جدول Albums حاوی دو کلید خارجی است و از این رو دو ردیف برای یک جدول در خروجی دریافت خواهید کرد.

sys.foreign_keys with DISTINCT

در این روش هم دوباره از DISTINCT استفاده می شود ولی این بار، کوئری روی View سیستمی sys.foreign_keys اعمال می شود.

SELECT DISTINCT

  OBJECT_SCHEMA_NAME(fk.parent_object_id) AS [Schema],

  OBJECT_NAME(fk.parent_object_id) AS [Table]

FROM sys.foreign_keys AS fk

ORDER BY [Schema], [Table];

+----------+---------+

| Schema   | Table   |

|----------+---------|

| dbo      | Albums  |

| dbo      | Artists |

+----------+---------+

حالا همین کوئری را بدون DISTINCT اجرا می کنیم:

SELECT

  OBJECT_SCHEMA_NAME(fk.parent_object_id) AS [Schema],

  OBJECT_NAME(fk.parent_object_id) AS [Table]

FROM sys.foreign_keys AS fk

ORDER BY [Schema], [Table];

جدولی که دو کلید خارجی دارد، دوباره تکرار می شود:

+----------+---------+

| Schema   | Table   |

|----------+---------|

| dbo      | Albums  |

| dbo      | Albums  |

| dbo      | Artists |

+----------+---------+

sys.foreign_keys with GROUP BY

دوباره از View سیستمی sys.foreign_keys برای این منظور استفاده شده است ولی این بار به جای DISTINCT از GROUP BY استفاده شده است.

SELECT

  OBJECT_SCHEMA_NAME(fk.parent_object_id) AS [Schema],

  OBJECT_NAME(fk.parent_object_id) AS [Table]

FROM sys.foreign_keys AS fk

GROUP BY

  OBJECT_SCHEMA_NAME(fk.parent_object_id),

  OBJECT_NAME(fk.parent_object_id);

+----------+---------+

| Schema   | Table   |

|----------+---------|

| dbo      | Albums  |

| dbo      | Artists |

+----------+---------+