All About Primary Key And Its Basics

In this series of articles, we will go deep into SQL Server from scratch and will gain knowledge of queries, optimization, and database administration. This is the first article of the series where we will learn about general SQL queries and their functioning. Images have been used wherever necessary so as to make you understand every command properly.

• All Queries which I am posting today you can use  directly on your query plan like copy, paste and execute this query.
• Each query has a valid column name and similarly I have shown in the form of image for proper understanding and proper usage

Find all Primary key in Give Database in following format,

 

 

SELECT i.name AS IndexName,
    OBJECT_NAME(ic.OBJECT_ID) AS TableName,
    COL_NAME(ic.OBJECT_ID, ic.column_id) AS ColumnName
FROM sys.indexes AS i
INNER JOIN sys.index_columns AS ic
ON i.OBJECT_ID = ic.OBJECT_ID
AND i.index_id = ic.index_id
WHERE i.is_primary_key = 1

Finding Constrains and Type of Constrain i.e. Primary and foreign key relation in the given database

 

 

SELECT OBJECT_NAME(OBJECT_ID) AS NameofConstraint,
    SCHEMA_NAME(schema_id) AS SchemaName,
    OBJECT_NAME(parent_object_id) AS TableName,
    type_desc AS ConstraintType
FROM sys.objects
WHERE type_desc IN('FOREIGN_KEY_CONSTRAINT', 'PRIMARY_KEY_CONSTRAINT')

Detailed level relationship and description of primary key and foreign key

 

 

SELECT f.name AS ForeignKey,
    SCHEMA_NAME(f.SCHEMA_ID) SchemaName,
    OBJECT_NAME(f.parent_object_id) AS TableName,
    COL_NAME(fc.parent_object_id, fc.parent_column_id) AS ColumnName,
    SCHEMA_NAME(o.SCHEMA_ID) ReferenceSchemaName,
    OBJECT_NAME(f.referenced_object_id) AS ReferenceTableName,
    COL_NAME(fc.referenced_object_id, fc.referenced_column_id) AS ReferenceColumnName
FROM sys.foreign_keys AS f
INNER JOIN sys.foreign_key_columns AS fc ON f.OBJECT_ID = fc.constraint_object_id
INNER JOIN sys.objects AS o ON o.OBJECT_ID = fc.referenced_object_id

Use the above snippets as per your requirement.

 

In most of the cases it's is going to be used in the Database Analysis where Database size and table are large and high in number.

 

Thus, we learned about the basic queries of SQL. If you have some doubt, or want to add some more information in this article, please feel free to write me in the comments section.

 

We will dive deeper into SQL in the upcoming articles of this series. Stay tuned!