When working with SQL Server, it is important to know if a table has a primary key or foreign key. This information can be useful for various reasons, such as ensuring data integrity and optimizing query performance. In this article, we will explore different methods to check for primary key and foreign key in SQL Server.

Checking for Primary Key

To check if a table has a primary key, you can use the following methods:

Method 1: Using sp_pkeys System Stored Procedure

You can execute the sp_pkeys system stored procedure to retrieve information about the primary key of a table. Here is an example:

EXEC sp_PKEYS product_master

The result will provide details about the primary key of the product_master table.

Method 2: Using Objectproperty Function

Another way to check for a primary key is by using the Objectproperty function. Here is an example:

SELECT *
FROM INFORMATION_SCHEMA.TABLES
WHERE OBJECTPROPERTY(OBJECT_ID(TABLE_NAME),'TABLEHASPRIMARYKEY')=1
AND TABLE_TYPE='BASE TABLE'
ORDER BY TABLE_NAME

This query will return a list of tables that have a primary key.

Checking for Foreign Key

To check if a table has a foreign key, you can use the following method:

Method: Using Objectproperty Function

Similar to checking for a primary key, you can use the Objectproperty function to determine if a table has a foreign key. Here is an example:

SELECT *
FROM INFORMATION_SCHEMA.TABLES
WHERE OBJECTPROPERTY(OBJECT_ID(TABLE_NAME),'TABLEHASFOREIGNKEY')=1
AND TABLE_TYPE='BASE TABLE'
ORDER BY TABLE_NAME

This query will provide a list of tables that have a foreign key.

Checking for Tables without Primary Key or Foreign Key

If you want to identify tables that do not have a primary key or foreign key, you can use the following method:

SELECT *
FROM INFORMATION_SCHEMA.TABLES
WHERE OBJECTPROPERTY(OBJECT_ID(TABLE_NAME),'TABLEHASPRIMARYKEY')=0
AND OBJECTPROPERTY(OBJECT_ID(TABLE_NAME),'TABLEHASFOREIGNKEY')=0
AND TABLE_TYPE='BASE TABLE'
ORDER BY TABLE_NAME

This query will return a list of tables that do not have a primary key or foreign key.

By using these methods, you can easily determine if a table has a primary key or foreign key in SQL Server. This knowledge can help you in various aspects of database management and query optimization.