Total Page Preview:   000000010071

SQL SERVER -Get or Find or Retrieve the list all Primary Key, Foreign Key and Unique Key into the Database

In this article I am going to explain How to get or find or retrieve the list all Primary Key, Foreign Key and Unique Key into the database.
There is Two difference method to get/find/retrieve the list all Primary Key, Foreign Key and Unique Key into the database.
Retrive the List of Primary Key using sys.objects: 
-----------------------Get all PRIMARY key into the Database-----------------------------------------

SELECT 
OBJECT_NAME(OBJECT_IDAS 'PrimaryKeyName',
SCHEMA_NAME(schema_idAS 'SchemaName',
OBJECT_NAME(parent_object_id) AS 'TableName',
type_desc AS 'ConstraintType/KeyType'
FROM sys.objects 
WHERE type_desc = 'PRIMARY_KEY_CONSTRAINT'

OUTPUT :

Retrive the List of Primary Key
 
Retrive the List of Foreign Key using sys.objects: 
-------------------------Get all FOREIGN key into the Database--------------------------------------

SELECT 
OBJECT_NAME(OBJECT_IDAS 'PrimaryKeyName',
SCHEMA_NAME(schema_idAS 'SchemaName',
OBJECT_NAME(parent_object_id) AS 'TableName',
type_desc AS 'ConstraintType/KeyType'
FROM sys.objects 
WHERE type_desc = 'FOREIGN_KEY_CONSTRAINT'

OUTPUT:

Retrive the List of Foreign Key
 
Retrive the List of Unique Key using sys.objects: 
-------------------------Get all UNIQUE key into the Database--------------------------------------

SELECT 
OBJECT_NAME(OBJECT_IDAS 'PrimaryKeyName',
SCHEMA_NAME(schema_idAS 'SchemaName',
OBJECT_NAME(parent_object_id) AS 'TableName',
type_desc AS 'ConstraintType/KeyType'
FROM sys.objects 
WHERE type_desc = 'UNIQUE_CONSTRAINT'

OUTPUT:

Retrive the List of Unique Key
Retrive the List of Primary/Foreign/Unique Key using INFORMATION_SCHEMA.KEY_COLUMN_USAGE View: 
-------Get all Primary Key, UniqueKay and FOREIGN Key into the database using view---------
SELECT [CONSTRAINT_CATALOG] AS 'DatabaseName'
      ,[CONSTRAINT_SCHEMA] AS 'KeySchemaName'
      ,[CONSTRAINT_NAME] AS 'KeyName'
      ,[TABLE_NAME] AS 'TableName'
      ,[COLUMN_NAME] AS 'ColumnName'
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE 
OUTPUT:
Retrive the List of Primary/Foreign/Unique Key using INFORMATION_SCHEMA.KEY_COLUMN_USAGE View
 
Thanks
 

Thank You

About Author

Brijesh Kumar

Database Developer

I have more then 6 years Experience in Microsoft Technologies - SQL Server Database, ETL Azure Cloud - Azure SQL Database, CosmosDB, Azure Data Factory, PowerBI, Web Job, Azure Function, Azure Storage, Web Apps, Powershall and Database Migration On-Premise to Azure Cloud.
LinkedIn : https://www.linkedin.com



Comments

Allum
21-Sep-2021
I think this is one of the most significant info for me. And i am glad reading your article. But want to remark on few general things, The web site style is wonderful, the articles is really nice : D. Good job, cheers
SQL tools
16-Nov-2016
Meaningful information. To know the new features in SQL Server 2016, visit at http://www.sql-datatools.com/2016/06/new-features-in-sql-server-2016.html
Danks
15-Jul-2021
I am actually happy to glance at this blog posts which contains plenty of helpful information, thanks for providing these data.

                           
                           

                           

                           

Recent Posts

How to get the alternate rows or records from table in sql server
07-Jun-2017
Data Warehouse Three Tier Architecture
20-Aug-2017
SQL Server - Fragmentation on azure sql database
07-Oct-2018
Limitations of Azure SQL Database
19-Sep-2019
PIVOT and UNPIVOT in SQL Server with Example
29-Jun-2017
SQL Server - Find all index with key columns on Azure SQL Database
02-Oct-2018
SQL Server - Find unused indexes on azure SQL database scripts
02-Oct-2018
Find Resource usage in Azure SQL Database using script or T-SQL
07-Oct-2018
How to Set client IP Address using the firewall security in Azure SQL Using Portal
29-Jun-2017
What is a Trigger and type of trigger in SQL server?
10-Sep-2021
What is Table Variable? Explain its advantages and disadvantages?
10-Sep-2021
Top 60 SQL Server Interview Questions Answers for freshers and experienced
17-Sep-2021
Cloud Data Engineer SQL Server Interview Questions Answers for freshers and experienced
31-Aug-2021
Top 16 performance query optimization techniques in SQL Database?
16-Sep-2021
Data warehouse Interview Questions Answers for freshers and experienced
16-Sep-2021
What are the types of User Define Function and difference between a nested UDF and recursive UDF in SQL server?
10-Sep-2021
What are the different types of Error Handling in SQL Server?
11-Sep-2021
What are the differences between OLTP and OLAP?
07-Sep-2021
How many types of dimension tables available in data warehouse?
16-Sep-2021
What are characteristics and advantages of stored procedure?
10-Sep-2021

Facebook User: