THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Adam Machanic

Adam Machanic, Boston-based SQL Server developer, shares his experiences with programming, monitoring, and performance tuning SQL Server. And the occasional battle with the query optimizer.

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!


Published Wednesday, July 12, 2006 10:32 PM by Adam Machanic
Filed under: ,

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Fredrik svard said:

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

April 19, 2007 2:49 PM
 

Adam Machanic said:

Thanks for the info, Fredrik!

May 22, 2007 2:08 PM
 

Ben Ootjers said:

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

October 5, 2007 3:55 AM
 

Ben Ootjers said:

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

Regards, Ben

October 5, 2007 3:58 AM
 

Jain said:

I am having problem with following statement:

AS (-- Create PK/UNIQUE constraints)

What is worng with it?  

January 7, 2009 7:45 AM
 

Adam Machanic said:

In the code those are square brackets, not parens.

January 7, 2009 11:29 AM
 

Jain said:

Thank you very much.  You saved me lots of time.

January 7, 2009 12:16 PM
 

Adam Machanic said:

Once upon a time it was 2004, and I wrote what I have to say was a pretty cool little script . (Yes,

April 4, 2010 11:19 PM
 

William said:

Adam, thanks a lot, great script..

July 1, 2010 10:39 AM
 

Boxta26 said:

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?

September 14, 2010 12:47 PM
 

Harris said:

Adam, this script is GOLDEN!!!!

Saved me tons of BORING work.

Thanks for sharing this.

September 30, 2010 3:33 PM
 

Nirav Raval said:

Adam, This is really awesome... This is what I'm looking for. Thank you very much.

January 12, 2011 2:30 AM
 

Adam Machanic said:

January 12, 2011 10:32 AM
 

shazamo said:

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

October 5, 2011 12:42 PM
 

Adam Machanic said:

shazamo,

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

--Adam

October 6, 2011 9:45 AM
 

Naomi said:

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.

November 21, 2011 5:58 PM
 

Adam Machanic said:

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?

November 21, 2011 10:06 PM

Leave a Comment

(required) 
(required) 
Submit

About Adam Machanic

Adam Machanic is a Boston-based SQL Server developer, writer, and speaker. He focuses on large-scale data warehouse performance and development, and is author of the award-winning SQL Server monitoring stored procedure, sp_WhoIsActive. Adam has written for numerous web sites and magazines, including SQLblog, Simple Talk, Search SQL Server, SQL Server Professional, CoDe, and VSJ. He has also contributed to several books on SQL Server, including "SQL Server 2008 Internals" (Microsoft Press, 2009) and "Expert SQL Server 2005 Development" (Apress, 2007). Adam regularly speaks at conferences and training events on a variety of SQL Server topics. He is a Microsoft Most Valuable Professional (MVP) for SQL Server, a Microsoft Certified IT Professional (MCITP), and an alumnus of the INETA North American Speakers Bureau.

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement