Generate script to create and drop Primary Key and Foreign Key in SQL Server

Generate script to create and drop Primary Key and Foreign Key in SQL Server

Primary Key and Foreign key is two main pillars to enforce referential integrity in relational database management system.

I had requirement to drop few tables which has Primary Key and that Primary Key has been used as a reference (Foreign Key) in few other table. After deleting table, I had to create that table again in our beta database, create same Primary Key and Foreign Key. Manually generating script to create/drop foreign key script for few tables is little tedious task and there is a chance that we miss some of the referenced table so I finally decided to quickly write down a script which can give me create and drop statement of primary key as well as all related foreign key once I pass the table name of primary key in filter area (WHERE clause).

I have used following system views/tables to cater the need of this article:

  • INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS
  • INFORMATION_SCHEMA.KEY_COLUMN_USAGE
  • Sys.Foreign_Keys

Here is the script I have generated with the help of above given system views and ran it in “AdventureWorks2012” database for “Department” table. You can change database name as well as table name as per your requirement.

[sourcecode language=”sql”]
USE AdventureWorks2012
GO
SELECT
*
,’ALTER TABLE [‘+ FKTableSchema +’].[‘ + FKTableName + ‘] DROP CONSTRAINT ‘ + ForeignKeyName AS FKDrop
,’ALTER TABLE [‘+ FKTableSchema +’].[‘ +FKTableName + ‘] ADD CONSTRAINT ‘ + ForeignKeyName + ‘ FOREIGN KEY(‘ +
FKColumnList +’) REFERENCES [‘+ PKTableSchema+’].[‘+ PKTableName + ‘] (‘+PKColumnList +’)’ AS FKCreate
,’ALTER TABLE [‘+ PKTableSchema +’].[‘ +PKTableName + ‘] DROP CONSTRAINT ‘ + PrimaryKeyName AS PKDrop
,’ALTER TABLE [‘+PKTableSchema +’].[‘ +PKTableName + ‘] ADD CONSTRAINT ‘ + PrimaryKeyName + ‘ PRIMARY KEY(‘+PKColumnList+’)’ AS PKCreate
FROM
(
SELECT
DISTINCT
FKTableSchema=(SELECT DISTINCT table_schema FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE constraint_name=rc.constraint_Name),
FKTableName=(SELECT DISTINCT table_Name FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE constraint_name=rc.constraint_Name),
rc.constraint_name AS ForeignKeyName,
FKColumnList=(SELECT left(t.column_name,len(t.column_name)-1) AS ‘ColumnList’ FROM
(
SELECT Column_Name + ‘,’ FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE constraint_name=rc.constraint_name
FOR XML PATH(”)
) AS t(column_Name)),
cu.table_schema AS PKTableSchema,
cu.table_name AS PKTableName,
cu.constraint_Name AS PrimaryKeyName,
PKColumnList=(SELECT left(t.column_name,len(t.column_name)-1) AS ‘ColumnList’ FROM
(
SELECT Column_Name + ‘,’ FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE constraint_name=cu.constraint_name
for xml path(”)
) AS t(column_Name))
FROM
INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS cu
INNER JOIN
INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS AS rc
ON
rc.Unique_Constraint_name= cu.Constraint_name
WHERE
table_name=’department’ –and table_schema=’sales’
) AS tab

GO[/sourcecode]

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Sharing IT tips at “Quick Learn

Note: Microsoft Books online is a default reference of all articles.

Author: Riteshshah

Ritesh Shah is a database professional with having 16+ years of experience in various domain of IT field. He is a author of many technical articles on Microsoft Technology and authored a book of SQL Server 2012 Performance Tuning cookbook. He loves work with different Microsoft Products.

2 thoughts on “Generate script to create and drop Primary Key and Foreign Key in SQL Server”

Comments are closed.