Home Uncategorized Script out PKs/UNIQUE constraints and referencing FKs

Script out PKs/UNIQUE constraints and referencing FKs

16

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!

SHARE
Previous articlePattern-based split string
Next article“Reflect” a TSQL routine
Adam Machanic helps companies get the most out of their SQL Server databases. He creates solid architectural foundations for high performance databases and is author of the award-winning SQL Server monitoring stored procedure, sp_WhoIsActive. Adam has contributed to numerous books on SQL Server development. A long-time Microsoft MVP for SQL Server, he speaks and trains at IT conferences across North America and Europe.

16 COMMENTS

  1. Hello,
    I used this great SP but changed it to do the same for all of the database so that I can truncate all tables and get a empty copy of my production system. I then noticed that the create of primary keys didn’t work for non dbo schemas so I changed the join with sysindexes to look lilke this
    LEFT JOIN sysindexes s ON s.name = TC.CONSTRAINT_NAME
    AND s.id = object_id(tc.table_schema + ‘.’ + TC.TABLE_NAME)
    then it work also for others schemas.
    Thanks for a great stored procedure.
    Fredrik

  2. Thanks Adam,  for the great script. Save a lot of work.
    By renaming the procedure to sp_* and creating the procedure in the master database it is usable whenever in a database.
    Regards, Ben

  3. Oops, just tested and it didn’t work. Will check later, why, probably because table also exist in master db.
    Regards, Ben

  4. It would be good to be able to do this as a separate drop and create script and for all tables in a given db. How easy would it be to do this?

  5. The first SELECT did not return all constraints (PKs and FKs); in my case it returned only PKs
    WHERE (TC.CONSTRAINT_TYPE IN (‘PRIMARY KEY’, ‘UNIQUE’) AND TC.TABLE_NAME = @TableName)
    OR (TC.CONSTRAINT_TYPE = ‘FOREIGN KEY’ AND FC.TABLE_NAME = @TableName)
    when I changed FC.TABLE_NAME = @TableName to TC.TABLE_NAME = @TableName, then it worked and gave me all PKs and FKs
    so the catch here is using FC. or TC., and because the query does LEFT JOIN with TABLE_CONSTRAINTS, TABLE_NAME here may or may not exist

  6. shazamo,
    Please see the prior comment: there is a much newer version available that handles things in better ways.
    –Adam

  7. Adam,
    Do you know how to script all type DEFAULT constraints which are created with the sp_binddefault? Our database uses such constraints (what a pity!) and I can not figure out how to script them for existing table so I can reproduce it.
    Thanks in advance.

  8. Hi Naomi,
    Apologies, but I don’t — can’t say that I’ve seen anyone use that feature. Those constraints can’t be found in sys.default_constraints?

LEAVE A REPLY

Please enter your comment!
Please enter your name here