Tag: Primary key

SCRIPT GALLERY, SQL

How to identify Primary and foreign key column information in SQL Server

Here is a small script to get information about the Primary Key column(s) and Foreign Key column(s) in SQL Server.

Script:


;WITH GETINFO AS
(
	SELECT	DBNAME = CONVERT(SYSNAME,DB_NAME()), 
				OWNERNAME = CONVERT(SYSNAME,SCHEMA_NAME(OBJ1.SCHEMA_ID)), 
				[PRIMARYTABLE NAME] = CONVERT(SYSNAME,OBJ1.NAME), 
				PKCOLUMN_NAME = CONVERT(SYSNAME,COL1.NAME), 
				[FOREIGNTABLE NAME]= CONVERT(SYSNAME,OBJ2.NAME), 
				FKCOLUMN_NAME = CONVERT(SYSNAME,COL2.NAME), 
				ORDINALPOSITION             = ISNULL(CONVERT(SMALLINT,FKCOL.CONSTRAINT_COLUMN_ID),0),
				[FOREIGNKEY NAME] = CONVERT(SYSNAME,OBJECT_NAME(FK.OBJECT_ID)), 
				[PRIMARYKEY NAME] = CONVERT(SYSNAME,I.NAME)
	FROM   SYS.ALL_OBJECTS OBJ1, SYS.ALL_OBJECTS OBJ2, SYS.ALL_COLUMNS COL1, SYS.ALL_COLUMNS COL2, SYS.FOREIGN_KEYS FK 
	INNER JOIN SYS.FOREIGN_KEY_COLUMNS FKCOL ON (FKCOL.CONSTRAINT_OBJECT_ID = FK.OBJECT_ID) 
	INNER JOIN SYS.INDEXES I  ON (FK.REFERENCED_OBJECT_ID = I.OBJECT_ID AND FK.KEY_INDEX_ID = I.INDEX_ID) 
	WHERE  OBJ1.OBJECT_ID = FK.REFERENCED_OBJECT_ID AND OBJ2.OBJECT_ID = FK.PARENT_OBJECT_ID 
				AND COL1.OBJECT_ID = FK.REFERENCED_OBJECT_ID AND COL2.OBJECT_ID = FK.PARENT_OBJECT_ID 
				AND COL1.COLUMN_ID = FKCOL.REFERENCED_COLUMN_ID AND COL2.COLUMN_ID = FKCOL.PARENT_COLUMN_ID
),SHOWINFO AS
(
	SELECT DISTINCT DBNAME,OWNERNAME,[PRIMARYTABLE NAME],[FOREIGNTABLE NAME],
           PRIMARYKEYCOLUMNNAMES=  REPLACE( 
			   (SELECT A.PKCOLUMN_NAME AS [data()]
				FROM GETINFO A
				WHERE A.[PRIMARYTABLE NAME] = B.[PRIMARYTABLE NAME] AND A.[FOREIGNKEY NAME] = B.[FOREIGNKEY NAME] AND A.[PRIMARYKEY NAME] = B.[PRIMARYKEY NAME]
				ORDER BY A.ORDINALPOSITION FOR XML PATH ('') ), ' ', ',') ,
           FOREIGNKEYCOLUMNNAMES=  REPLACE( 
			   (SELECT A.FKCOLUMN_NAME AS [data()]
                FROM GETINFO A
                WHERE A.[PRIMARYTABLE NAME] = B.[PRIMARYTABLE NAME] AND A.[FOREIGNKEY NAME] = B.[FOREIGNKEY NAME] AND A.[PRIMARYKEY NAME] = B.[PRIMARYKEY NAME]
                ORDER BY A.ORDINALPOSITION FOR XML PATH ('') ), ' ', ',') 
	FROM GETINFO B 
)SELECT * FROM SHOWINFO
Myths, SQL

Myth: Primary Key and Clustered index

Myth:
“When you create a PRIMARY KEY on table, Clustered index is creating by default”
Or
“Primary Key will create a clustered index by default”

Explanation:
I heard these statements many times and recenty, l heard from one of my collegues as well. So thought of sharing with you that the statement is a myth. Though, MSDN article says it very clearly, some of people are not learning or reading it with its completeness.

Please find the Excerpt from MSDN Article:

When you create a PRIMARY KEY constraint, a unique clustered index on the column or columns is automatically created if a clustered index on the table does not already exist and you do not specify a unique nonclustered index..

Now, Let us look at something practical.

First, I would like to create a table with no clustered index(Heap) and define a primary key on the table.[Heap is a table without clustered index.]

Create Table PRIMARYKEY_MYTH(Column1 int NOT NULL,Column2 int)

Alter Table PRIMARYKEY_MYTH Add constraint PK__PRIMARYKEY_MYTH__Column1 PRIMARY KEY(Column1)

sp_help 'PRIMARYKEY_MYTH'

PK__PRIMARYKEY_MYTH__Column1	clustered, unique, primary key located on PRIMARY	

Drop table PRIMARYKEY_MYTH

From the above, we can clearly say that Primary Key is creating a Clustered index on a table.

Second, we need to create a Primary key on a clustered table(Already a clustered index is created on a table)

Create Table PRIMARYKEY_MYTH(Column1 int NOT NULL,Column2 int)

create clustered index IX__PRIMARYKEY_MYTH__Col on PRIMARYKEY_MYTH(Column2)

Alter Table PRIMARYKEY_MYTH Add constraint PK__PRIMARYKEY_MYTH__Column1 PRIMARY KEY(Column1)

sp_help 'PRIMARYKEY_MYTH'
--Results of the sp_help

IX__PRIMARYKEY_MYTH__Col	clustered located on PRIMARY		                
PK__PRIMARYKEY_MYTH__Column1	nonclustered, unique, primary key located on PRIMARY	

Drop table PRIMARYKEY_MYTH

Here, it is very clear, Primary Key on a clustered table is only creating a nonclustered unique index on the table, not the Clustered index.

Hence, I would request you to pronounce the complete statement as 

When you create a PRIMARY KEY constraint, a unique clustered index on the column or columns is automatically created if a clustered index on the table does not already exist and you do not specify a unique non clustered index..

I would like to request you to share your thoughts on the same.