در این پست تصمیم داریم با روش های مختلفی با شما دوستان نشان دهیم که جداولی از دیتابیس در SQL Server را که فاقد primary key هستند را لیست کنید. تابع مذکور آرگومان TableHasPrimaryKey را می تواند دریافت کند و اگر در خروجی 1 برگشت نشان از وجود و اگر 0 برگشت نشان از عدم وجود کلید اصلی است.


اسکریپت های زیر نام جدول و نام Schema را برمی گردانند و اگر نیاز به ستون های بیشتر دارید به راحتی می توانید کدها را تغییر دهید.

OBJECTPROPERTY() with sys.tables

در کام نخست به سراغ View سیستمی sys.tables می رویم. این View برای جدولی که تسوط کاربر ایجاد شده یک ردیف برمیگرداند. هنگامی که ما از OBJECTPROPERTY برای فیلتر کردن نتایج براساس صفر بودن ویژگی TableHasPrimaryKey استفاده می کنیم، فقط جداولی که فاقد primary key هستند به ما نمایش داده می شوند.

USE Test;

SELECT

  SCHEMA_NAME(schema_id) AS [Schema],

  name AS [Table]

FROM sys.tables

WHERE OBJECTPROPERTY(object_id, 'TableHasPrimaryKey') = 0

ORDER BY [Schema], [Table];

Changed database context to 'Test'.

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

| Schema   | Table              |

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

| dbo      | Datetime2Test      |

| dbo      | Datetime2Test2     |

| dbo      | DatetimeoffsetTest |

| dbo      | Individual         |

| dbo      | Occupation         |

| dbo      | Team               |

| dbo      | TimeTest           |

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

(7 rows affected)

در این مثال نام دیتابیس test بوده که در آن جداولی بدون کلید اصلی هستند. اگر تمام جداول دارای کلید اصلی باشند، در خروجی کوئری چیزی مشاهده نخواهید کرد:

USE Music;

SELECT

  SCHEMA_NAME(schema_id) AS [Schema],

  name AS [Table]

FROM sys.tables

WHERE OBJECTPROPERTY(object_id, 'TableHasPrimaryKey') = 0

ORDER BY [Schema], [Table];

Changed database context to 'Music'.

(0 rows affected)

OBJECTPROPERTY() with INFORMATION_SCHEMA.TABLES

این روش هم مشابه روش قبلی است با این تفاوت که از View سیستمی INFORMATION_SCHEMA.TABLES استفاده می شود.

USE Test;

SELECT

  TABLE_SCHEMA,

  TABLE_NAME

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

TABLE_TYPE='BASE TABLE'

ORDER BY TABLE_SCHEMA, TABLE_NAME;

Changed database context to 'Test'.

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

| TABLE_SCHEMA   | TABLE_NAME         |

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

| dbo            | Datetime2Test      |

| dbo            | Datetime2Test2     |

| dbo            | DatetimeoffsetTest |

| dbo            | Individual         |

| dbo            | Occupation         |

| dbo            | Team               |

| dbo            | TimeTest           |

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

(7 rows affected)

OBJECTPROPERTY() with sys.objects

در سومین روش از View سیستمی دیگری با نام sys.objects استفاده خواهیم کرد. این یک مقایسه کلی با دو مورد قبلی است و اطلاعات مربوط به اشیا دارای schema (نه فقط جداول) را برمی گرداند. از این رو باید مقدار U را در قسمت WHERE هنگام استفاده از View ی مذکور را درج کنیم که نشان از user-defined table دارد. حالا که جدول ها را داریم، با استفاده از تابع OBJECTPROPERTY فقط جداولی از دیتابیس مربوطه که فاقد کلید اصلی هستند را لیست می کنیم.

USE Test;

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)), 'TableHasPrimaryKey') = 0

ORDER BY [Schema], [Table]

Changed database context to 'Test'.

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

| Schema   | Table              |

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

| dbo      | Datetime2Test      |

| dbo      | Datetime2Test2     |

| dbo      | DatetimeoffsetTest |

| dbo      | Individual         |

| dbo      | Occupation         |

| dbo      | Team               |

| dbo      | TimeTest           |

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

(7 rows affected)

همچنین با اضافه کردن type_desc = USER_TABLE همین نتیجه فوق را می توانید بگیرید:

USE Test;

SELECT

  SCHEMA_NAME(schema_id) AS [Schema],

  name AS [Table]

FROM sys.objects

WHERE type_desc = 'USER_TABLE'


AND OBJECTPROPERTY(OBJECT_ID(CONCAT(SCHEMA_NAME(schema_id), '.', name)), 'TableHasPrimaryKey') = 0

ORDER BY [Schema], [Table]

Changed database context to 'Test'.

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

| Schema   | Table              |

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

| dbo      | Datetime2Test      |

| dbo      | Datetime2Test2     |

| dbo      | DatetimeoffsetTest |

| dbo      | Individual         |

| dbo      | Occupation         |

| dbo      | Team               |

| dbo      | TimeTest           |

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

(7 rows affected)