posted on Tuesday, February 22, 2005 12:02 PM by amachanic

Script out PKs/UNIQUE constraints and referencing FKs

In the course of my work, I occasionally need to cluster a primary key that's nonclustered, or go the other way, or make some other modification to a primary key...

But it's a hassle! All of the foreign keys need to be dropped, the PK needs to be dropped, and then everything needs to be re-created. Scripting all of that stuff out can be very annoying.

With that annoyance in mind, I've written this stored procedure. Put Query Analyzer into Results in Text mode and run ScriptTableConstraints for a table. It will script out DROPs and CREATEs for all primary keys, unique keys, and any foreign keys that reference them -- in the right order. Foreign keys will be dropped first, then any non-clustered PK or UNIQUE constraints, then clustered constraints. Keys will be scripted for re-creation in the reverse order.

... And that's it. This should take some of the annoyance out of doing this kind of work. Here is the stored procedure: 

CREATE PROCEDURE ScriptTableConstraints
	@TableName VARCHAR(100)
AS
BEGIN
	SET NOCOUNT ON

	SELECT 'ALTER TABLE [' + TC.TABLE_SCHEMA + '].[' + TC.TABLE_NAME + ']' + CHAR(13)+CHAR(10)+ 
		'DROP CONSTRAINT [' + TC.CONSTRAINT_NAME + ']' + CHAR(13)+CHAR(10)+CHAR(13)+CHAR(10) 
		AS [-- Drop Constraints]
	FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
	LEFT JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC ON RC.CONSTRAINT_NAME = TC.CONSTRAINT_NAME
	LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FC ON FC.CONSTRAINT_NAME = RC.UNIQUE_CONSTRAINT_NAME
	WHERE (TC.CONSTRAINT_TYPE IN ('PRIMARY KEY', 'UNIQUE') AND TC.TABLE_NAME = @TableName)
		OR (TC.CONSTRAINT_TYPE = 'FOREIGN KEY' AND FC.TABLE_NAME = @TableName)
	ORDER BY FC.TABLE_NAME DESC,
		INDEXPROPERTY(OBJECT_ID(TC.TABLE_NAME), TC.CONSTRAINT_NAME, 'IsClustered') ASC
	
	
	SELECT 'ALTER TABLE [' + TC.TABLE_SCHEMA + '].[' + TC.TABLE_NAME + ']' + CHAR(13)+CHAR(10)+ 
		'ADD CONSTRAINT [' + TC.CONSTRAINT_NAME + ']' + CHAR(13)+CHAR(10)+
		'  ' + TC.CONSTRAINT_TYPE + 
			CASE INDEXPROPERTY(OBJECT_ID(TC.TABLE_NAME), TC.CONSTRAINT_NAME, 'IsClustered') 
				WHEN 1 THEN ' CLUSTERED' 
				ELSE ' NONCLUSTERED' 
			END + CHAR(13)+CHAR(10)+
		'  (' +
			MAX(CASE KCU.ORDINAL_POSITION WHEN 1 THEN '[' + KCU.COLUMN_NAME + ']' ELSE '' END) +
			MAX(CASE KCU.ORDINAL_POSITION WHEN 2 THEN ', [' + KCU.COLUMN_NAME + ']' ELSE '' END) +
			MAX(CASE KCU.ORDINAL_POSITION WHEN 3 THEN ', [' + KCU.COLUMN_NAME + ']' ELSE '' END) +
			MAX(CASE KCU.ORDINAL_POSITION WHEN 4 THEN ', [' + KCU.COLUMN_NAME + ']' ELSE '' END) +
			MAX(CASE KCU.ORDINAL_POSITION WHEN 5 THEN ', [' + KCU.COLUMN_NAME + ']' ELSE '' END) +
			MAX(CASE KCU.ORDINAL_POSITION WHEN 6 THEN ', [' + KCU.COLUMN_NAME + ']' ELSE '' END) +
			MAX(CASE KCU.ORDINAL_POSITION WHEN 7 THEN ', [' + KCU.COLUMN_NAME + ']' ELSE '' END) +
			MAX(CASE KCU.ORDINAL_POSITION WHEN 8 THEN ', [' + KCU.COLUMN_NAME + ']' ELSE '' END) +
			MAX(CASE KCU.ORDINAL_POSITION WHEN 9 THEN ', [' + KCU.COLUMN_NAME + ']' ELSE '' END) +
			MAX(CASE KCU.ORDINAL_POSITION WHEN 10 THEN ', [' + KCU.COLUMN_NAME + ']' ELSE '' END) +
			MAX(CASE KCU.ORDINAL_POSITION WHEN 11 THEN ', [' + KCU.COLUMN_NAME + ']' ELSE '' END) +
			MAX(CASE KCU.ORDINAL_POSITION WHEN 12 THEN ', [' + KCU.COLUMN_NAME + ']' ELSE '' END) +
			MAX(CASE KCU.ORDINAL_POSITION WHEN 13 THEN ', [' + KCU.COLUMN_NAME + ']' ELSE '' END) +
			MAX(CASE KCU.ORDINAL_POSITION WHEN 14 THEN ', [' + KCU.COLUMN_NAME + ']' ELSE '' END) +
			MAX(CASE KCU.ORDINAL_POSITION WHEN 15 THEN ', [' + KCU.COLUMN_NAME + ']' ELSE '' END) +
			MAX(CASE KCU.ORDINAL_POSITION WHEN 16 THEN ', [' + KCU.COLUMN_NAME + ']' ELSE '' END) +
		')' + CHAR(13)+CHAR(10)+
		'WITH FILLFACTOR = ' + 
			CONVERT(varchar(3), --Replace 0 FILLFACTOR with 100
				ISNULL(NULLIF(
					INDEXPROPERTY(OBJECT_ID(TC.TABLE_NAME), TC.CONSTRAINT_NAME, 'IndexFillFactor'), 
				0), 100)
				) + CHAR(13)+CHAR(10)+
		'ON [' + sfg.groupname + ']' + CHAR(13)+CHAR(10)+CHAR(13)+CHAR(10)
		AS [-- Create PK/UNIQUE Constraints]
	FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
	JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU ON TC.TABLE_NAME = KCU.TABLE_NAME
			AND TC.CONSTRAINT_NAME = KCU.CONSTRAINT_NAME
	LEFT JOIN sysindexes s ON s.name = TC.CONSTRAINT_NAME
		AND s.id = object_id(TC.TABLE_NAME)
	LEFT JOIN sysfilegroups sfg ON sfg.groupid = s.groupid
	WHERE TC.CONSTRAINT_TYPE IN ('PRIMARY KEY', 'UNIQUE')
		AND TC.TABLE_NAME = @TableName
	GROUP BY 
		TC.TABLE_SCHEMA,
		TC.TABLE_NAME,
		TC.CONSTRAINT_NAME,
		TC.CONSTRAINT_TYPE,
		sfg.groupname
	ORDER BY INDEXPROPERTY(OBJECT_ID(TC.TABLE_NAME), TC.CONSTRAINT_NAME, 'IsClustered') DESC
	
	SELECT 'ALTER TABLE [' + FC.TABLE_SCHEMA + '].[' + FC.TABLE_NAME + ']' + CHAR(13)+CHAR(10)+ 
		'ADD CONSTRAINT [' + FC.CONSTRAINT_NAME + '] ' + FC.CONSTRAINT_TYPE + CHAR(13)+CHAR(10)+
		'  (' +
			MAX(CASE KCU_FK.ORDINAL_POSITION WHEN 1 THEN '[' + KCU_FK.COLUMN_NAME + ']' ELSE '' END) +
			MAX(CASE KCU_FK.ORDINAL_POSITION WHEN 2 THEN ', [' + KCU_FK.COLUMN_NAME + ']' ELSE '' END) +
			MAX(CASE KCU_FK.ORDINAL_POSITION WHEN 3 THEN ', [' + KCU_FK.COLUMN_NAME + ']' ELSE '' END) +
			MAX(CASE KCU_FK.ORDINAL_POSITION WHEN 4 THEN ', [' + KCU_FK.COLUMN_NAME + ']' ELSE '' END) +
			MAX(CASE KCU_FK.ORDINAL_POSITION WHEN 5 THEN ', [' + KCU_FK.COLUMN_NAME + ']' ELSE '' END) +
			MAX(CASE KCU_FK.ORDINAL_POSITION WHEN 6 THEN ', [' + KCU_FK.COLUMN_NAME + ']' ELSE '' END) +
			MAX(CASE KCU_FK.ORDINAL_POSITION WHEN 7 THEN ', [' + KCU_FK.COLUMN_NAME + ']' ELSE '' END) +
			MAX(CASE KCU_FK.ORDINAL_POSITION WHEN 8 THEN ', [' + KCU_FK.COLUMN_NAME + ']' ELSE '' END) +
			MAX(CASE KCU_FK.ORDINAL_POSITION WHEN 9 THEN ', [' + KCU_FK.COLUMN_NAME + ']' ELSE '' END) +
			MAX(CASE KCU_FK.ORDINAL_POSITION WHEN 10 THEN ', [' + KCU_FK.COLUMN_NAME + ']' ELSE '' END) +
			MAX(CASE KCU_FK.ORDINAL_POSITION WHEN 11 THEN ', [' + KCU_FK.COLUMN_NAME + ']' ELSE '' END) +
			MAX(CASE KCU_FK.ORDINAL_POSITION WHEN 12 THEN ', [' + KCU_FK.COLUMN_NAME + ']' ELSE '' END) +
			MAX(CASE KCU_FK.ORDINAL_POSITION WHEN 13 THEN ', [' + KCU_FK.COLUMN_NAME + ']' ELSE '' END) +
			MAX(CASE KCU_FK.ORDINAL_POSITION WHEN 14 THEN ', [' + KCU_FK.COLUMN_NAME + ']' ELSE '' END) +
			MAX(CASE KCU_FK.ORDINAL_POSITION WHEN 15 THEN ', [' + KCU_FK.COLUMN_NAME + ']' ELSE '' END) +
			MAX(CASE KCU_FK.ORDINAL_POSITION WHEN 16 THEN ', [' + KCU_FK.COLUMN_NAME + ']' ELSE '' END) +
		')' + CHAR(13)+CHAR(10)+
		'REFERENCES [' + TC.TABLE_SCHEMA + '].[' + TC.TABLE_NAME + ']' + CHAR(13)+CHAR(10)+
		'  (' +
			MAX(CASE KCU_PK.ORDINAL_POSITION WHEN 1 THEN '[' + KCU_PK.COLUMN_NAME + ']' ELSE '' END) +
			MAX(CASE KCU_PK.ORDINAL_POSITION WHEN 2 THEN ', [' + KCU_PK.COLUMN_NAME + ']' ELSE '' END) +
			MAX(CASE KCU_PK.ORDINAL_POSITION WHEN 3 THEN ', [' + KCU_PK.COLUMN_NAME + ']' ELSE '' END) +
			MAX(CASE KCU_PK.ORDINAL_POSITION WHEN 4 THEN ', [' + KCU_PK.COLUMN_NAME + ']' ELSE '' END) +
			MAX(CASE KCU_PK.ORDINAL_POSITION WHEN 5 THEN ', [' + KCU_PK.COLUMN_NAME + ']' ELSE '' END) +
			MAX(CASE KCU_PK.ORDINAL_POSITION WHEN 6 THEN ', [' + KCU_PK.COLUMN_NAME + ']' ELSE '' END) +
			MAX(CASE KCU_PK.ORDINAL_POSITION WHEN 7 THEN ', [' + KCU_PK.COLUMN_NAME + ']' ELSE '' END) +
			MAX(CASE KCU_PK.ORDINAL_POSITION WHEN 8 THEN ', [' + KCU_PK.COLUMN_NAME + ']' ELSE '' END) +
			MAX(CASE KCU_PK.ORDINAL_POSITION WHEN 9 THEN ', [' + KCU_PK.COLUMN_NAME + ']' ELSE '' END) +
			MAX(CASE KCU_PK.ORDINAL_POSITION WHEN 10 THEN ', [' + KCU_PK.COLUMN_NAME + ']' ELSE '' END) +
			MAX(CASE KCU_PK.ORDINAL_POSITION WHEN 11 THEN ', [' + KCU_PK.COLUMN_NAME + ']' ELSE '' END) +
			MAX(CASE KCU_PK.ORDINAL_POSITION WHEN 12 THEN ', [' + KCU_PK.COLUMN_NAME + ']' ELSE '' END) +
			MAX(CASE KCU_PK.ORDINAL_POSITION WHEN 13 THEN ', [' + KCU_PK.COLUMN_NAME + ']' ELSE '' END) +
			MAX(CASE KCU_PK.ORDINAL_POSITION WHEN 14 THEN ', [' + KCU_PK.COLUMN_NAME + ']' ELSE '' END) +
			MAX(CASE KCU_PK.ORDINAL_POSITION WHEN 15 THEN ', [' + KCU_PK.COLUMN_NAME + ']' ELSE '' END) +
			MAX(CASE KCU_PK.ORDINAL_POSITION WHEN 16 THEN ', [' + KCU_PK.COLUMN_NAME + ']' ELSE '' END) +
		')' + CHAR(13)+CHAR(10)+CHAR(13)+CHAR(10)
		AS [-- Create FK Constraints]
	FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
	JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU_PK ON TC.TABLE_NAME = KCU_PK.TABLE_NAME
			AND TC.CONSTRAINT_NAME = KCU_PK.CONSTRAINT_NAME
	JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC ON RC.UNIQUE_CONSTRAINT_NAME = TC.CONSTRAINT_NAME
	JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FC ON FC.CONSTRAINT_NAME = RC.CONSTRAINT_NAME
	JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU_FK ON FC.TABLE_NAME = KCU_FK.TABLE_NAME
			AND FC.CONSTRAINT_NAME = KCU_FK.CONSTRAINT_NAME
	WHERE TC.CONSTRAINT_TYPE IN ('PRIMARY KEY', 'UNIQUE')
		AND TC.TABLE_NAME = @TableName
	GROUP BY 
		FC.TABLE_SCHEMA,
		FC.TABLE_NAME,
		FC.CONSTRAINT_NAME,
		FC.CONSTRAINT_TYPE,
		TC.TABLE_SCHEMA,
		TC.TABLE_NAME
END

Let me know if this helps you or if there's some modification that should be made... Enjoy!

Comments

# New script posted: Script out PK/UNIQUE constraints and referencing FKs @ Tuesday, February 22, 2005 12:06 PM

New script posted: Script out PK/UNIQUE constraints and referencing FKs

amachanic

# re: Script out PKs/UNIQUE constraints and referencing FKs @ Monday, August 08, 2005 10:23 AM

Thank you so much for the procedure but how can I make this procedure work for databases, instead of tables?

I have more than 100 tables that I need to drop/create constraints and I dont want to do it one by one.

Halil

# Script out PKs/UNIQUE constraints and referencing FKs @ Thursday, July 13, 2006 12:32 AM

Originally posted here.

In the course of my work, I occasionally need to cluster a primary key
that's...

Anonymous

# Script out PKs/UNIQUE constraints and referencing FKs @ Monday, January 08, 2007 2:33 PM

Originally posted here . In the course of my work, I occasionally need to cluster a primary key that's

Anonymous