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:
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
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
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
· BISQL # 2 :How To Create Solution? Why and What is its Use!!
· BISQL # 3 :Time and Date Query code and Analysis Part -I
· BISQL # 4 :Identifying and Search of Relationship’s
· BISQL # 5 :Analyzing the Size of Database and Size of table
· BISQL # 6 :Identifying Running Query and Last Modified Query
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
Copyright © 2011 – 2012 Vishal Pawar
SQL Server Mentalist 
hi,
it is good to understand what exactly use of system VIEWS and TVFunctions