November 7, 2018 08:44 by
Peter
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.