Home > Query, SQL > SQL SERVER – BISQL #1 : IT’s All About Primary Key and Basics of It !!!

SQL SERVER – BISQL #1 : IT’s All About Primary Key and Basics of It !!!

Hi Friends,

  • 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:

image

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

 

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

image

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') 

 

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

image

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.

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

For more interesting information on SQL we can also look into similar topics such as

Hope you will like this post on Primary Key and Basics of It.

If you really like reading my blog and understood at least few thing then please don’t forget to subscribe my blog.

If you want daily link and analysis or interesting link go to following website which will give @ your inbox please subscribe our following link resource blog : Link Resource Website

For More information related to BI World visit my Mentalist Blog

Link Resource Blog >> Daily Interesting links

SQL Server Mentalist >> SQL Learning Blog

Business Intelligence Mentalist >> BI World

Connect With me on

| FaceBook |Twitter | linkedIn| Google+ | WordPress | RSS |

Copyright © 2011 – 2012 Vishal Pawar

Advertisements
  1. abhijeet
    June 1, 2011 at 11:48 am

    hi,
    it is good to understand what exactly use of system VIEWS and TVFunctions

  1. June 30, 2011 at 10:15 pm
  2. July 31, 2011 at 9:31 pm

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s

%d bloggers like this: