Script to generate only constraints

Question

Post reply

Script to generate only constraints

Nikita-395727

SSC Eights!

Points: 898
More actions
August 9, 2007 at 1:50 pm

#175829

Hi,
Does anyone know how to script only constraints ( pk, fk, defaults ) and indexes only and not the actual table creation script?
Thanks,

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Piotr.Rodak SSCrazy Eights Points: 9376 More actions · Answer 1

Probably you have to use SMO to do this.

...and your only reply is slàinte mhath

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 2

Just a list or to generate the actual code to create the constraints?

Also, why do you want to do this? Might be important...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

jerry-621596 SSCertifiable Points: 6096 More actions · Answer 3

I also need to do this.

My situation is that I have several production databases that I need to add new constraints and PKs to for an update/upgrade to the related software.

I don't want, or need all of the other stuff that the Generate scripts wizard produces. That only means that I have to edit out all of the Create Table stuff for nearly 200 tables.

SQL Compare is not an option right now. All financial resources have been committed to the actual software upgrade.

Thanks in advance for your assistance in input.

Jerry

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 4

Understood on the SQL Compare. Not sure I have anything simple to help. I know that generating all the tables en masse produces a script where FK's and the like are generated near the end which makes editing pretty easy but I don't recall what it does with other constraints.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

WayneS SSC Guru Points: 95460 More actions · Answer 5

You'll want to query the system views directly:

select * from sys.key_constraints

select * from sys.default_constraints

select * from sys.foreign_keys

select * from sys.check_constraints

select * from sys.indexes

It should be pretty obvious what each is for.

The key_constraints view has both primary key and unique constraints. Join to sys.indexes, sys.index_columns and sys.columns for the columns involved.

sys.foreign_keys - join to sys.foreign_key_columns and sys.columns to get the column information. Join to sys.tables for table information.

The check_constraints and default_constraints have a definition column for what the constraint is.

Join sys.indexes to sys.index_columns and sys.columns for the index definitions. Ensure you order it by the index_column_id, and watch out for the is_included_column for adding these.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2