New Era Begin For SQL Server and Various Query Learning Session

  • BISQL#1:IT’s All About Primary Key and Basics of It !!! my new post for SQL For only todays purpose I am posting this on this website so u will have an idea regarding content and postings.
  • From today I have Started series of SQL statements which gone cover entire analysis of SQL Statement for fast SSIS/BI Development
  • All Query which I am posting today you can use this directly on your query plan like copy,paste and execute this query.
  • Each query have valid column name and similarly I have shown in the form of image for proper understanding and proper usage

1.Find all Primary key in Give Database in following format:


SELECT AS IndexName,


COL_NAME(ic.OBJECT_ID,ic.column_id) AS ColumnName

FROM sys.indexes AS i

INNER JOIN sys.index_columns AS ic


AND i.index_id = ic.index_id

WHERE i.is_primary_key = 1

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



SCHEMA_NAME(schema_id) AS SchemaName,

OBJECT_NAME(parent_object_id) AS TableName,

type_desc AS ConstraintType

FROM sys.objects 


3.Detailed level relationship and description of primary key and foreign key


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

Most of the case its is going to be use in the Database Analysis where Database size and table are large and high in number

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s