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.

 

HostForLIFE.eu SQL Server 2016 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.