Total Page Preview:   000000010652

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

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.
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

                           
                           

                           

                           

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 unused indexes on azure SQL database scripts
02-Oct-2018
SQL Server - Find all index with key columns on Azure SQL Database
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
Top 60 SQL Server Interview Questions Answers for freshers and experienced
17-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
Top 16 performance query optimization techniques in SQL Database?
16-Sep-2021
What is a Trigger and type of trigger in SQL server?
10-Sep-2021
How many types of dimension tables available in data warehouse?
16-Sep-2021
What is Table Variable? Explain its advantages and disadvantages?
10-Sep-2021
Data warehouse Interview Questions Answers for freshers and experienced
16-Sep-2021
What are the differences between OLTP and OLAP?
07-Sep-2021
What are the different types of Error Handling in SQL Server?
11-Sep-2021
Cloud Data Engineer SQL Server Interview Questions Answers for freshers and experienced
31-Aug-2021
What are characteristics and advantages of stored procedure?
10-Sep-2021

Facebook User: