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.

 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

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.

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

  1. Pingback: Milestone of 500+ article

Comments are closed.