Home Uncategorized Script out PKs/UNIQUE constraints and referencing FKs

    Script out PKs/UNIQUE constraints and referencing FKs

    835
    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!

    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. I am having problem with following statement:
      AS (– Create PK/UNIQUE constraints)
      What is worng with it?  

    5. 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?

    6. 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

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

    8. 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.

    9. 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?

    Comments are closed.