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

Total Page Preview: 000000011155

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_ID) AS 'PrimaryKeyName',
SCHEMA_NAME(schema_id) AS '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 Foreign Key using sys.objects:

-------------------------Get all FOREIGN key into the Database--------------------------------------

OUTPUT:

Retrive the List of Unique Key using sys.objects:

-------------------------Get all UNIQUE key into the Database--------------------------------------

OUTPUT:

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

http://www.sql-datatools.com

Relpy

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.

http://fcialisj.com

Relpy

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

https://www.flickr.com/people/jonilahdesta/

Relpy






Notify me of new comments via email. Notify me of new posts via email.