Script out PKs/UNIQUE constraints and referencing FKs

re: Script out PKs/UNIQUE constraints and referencing FKs

Fredrik svard — Thu, 19 Apr 2007 18:49:48 GMT

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

re: Script out PKs/UNIQUE constraints and referencing FKs

Adam Machanic — Tue, 22 May 2007 18:08:56 GMT

Thanks for the info, Fredrik!

re: Script out PKs/UNIQUE constraints and referencing FKs

Ben Ootjers — Fri, 05 Oct 2007 07:55:35 GMT

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

re: Script out PKs/UNIQUE constraints and referencing FKs

Ben Ootjers — Fri, 05 Oct 2007 07:58:40 GMT

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

Regards, Ben

re: Script out PKs/UNIQUE constraints and referencing FKs

Jain — Wed, 07 Jan 2009 12:45:36 GMT

I am having problem with following statement:

AS (-- Create PK/UNIQUE constraints)

What is worng with it?

re: Script out PKs/UNIQUE constraints and referencing FKs

Adam Machanic — Wed, 07 Jan 2009 16:29:31 GMT

In the code those are square brackets, not parens.

re: Script out PKs/UNIQUE constraints and referencing FKs

Jain — Wed, 07 Jan 2009 17:16:29 GMT

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

Rejuvenated: Script Creates and Drops for Candidate Keys and Referencing Foreign Keys

Adam Machanic — Mon, 05 Apr 2010 03:19:21 GMT

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

re: Script out PKs/UNIQUE constraints and referencing FKs

William — Thu, 01 Jul 2010 14:39:32 GMT

Adam, thanks a lot, great script..

re: Script out PKs/UNIQUE constraints and referencing FKs

Boxta26 — Tue, 14 Sep 2010 16:47:51 GMT

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?

re: Script out PKs/UNIQUE constraints and referencing FKs

Harris — Thu, 30 Sep 2010 19:33:35 GMT

Adam, this script is GOLDEN!!!!

Saved me tons of BORING work.

Thanks for sharing this.

re: Script out PKs/UNIQUE constraints and referencing FKs

Nirav Raval — Wed, 12 Jan 2011 07:30:34 GMT

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

re: Script out PKs/UNIQUE constraints and referencing FKs

Adam Machanic — Wed, 12 Jan 2011 15:32:44 GMT

Hi all,

Please make sure to check out the newer version:

http://sqlblog.com/blogs/adam_machanic/archive/2010/04/04/rejuvinated-script-creates-and-drops-for-candidate-keys-and-referencing-foreign-keys.aspx

re: Script out PKs/UNIQUE constraints and referencing FKs

shazamo — Wed, 05 Oct 2011 16:42:34 GMT

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

re: Script out PKs/UNIQUE constraints and referencing FKs

Adam Machanic — Thu, 06 Oct 2011 13:45:02 GMT

shazamo,

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

--Adam

re: Script out PKs/UNIQUE constraints and referencing FKs

Naomi — Mon, 21 Nov 2011 22:58:53 GMT

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.

re: Script out PKs/UNIQUE constraints and referencing FKs

Adam Machanic — Tue, 22 Nov 2011 03:06:08 GMT

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?