Constraint on Column

[TGM]

Hi

I import sevral tables from a SQL Db to another SQL DB.
During this immport, I loose all the constraints !!!!

Two questions:
- Is it possible to import tables with the CONSTRAINT ?
- If not, how could I use ALTER Table to Add COnstraint to the required column. I looked at the doc but I don't understand where I spicify the column on which I want to add a constraint.

Thanks for your help

Thierry

 

[MeanGreen]

How did you import your tables? One way to get your constraints is within the Enterprise Manager, highlight the table you want to import. Right click and under "All Task" choose "Generate SQL Scripts". After the pop-up box comes up, choose the third tab "Options". Then you check the box "Script Primary Keys, Foreign Keys, Defaults, and Check Constraints". I usually just go back to Tab 1 "General" and Preview the Script. During Preview, I can copy and paste what I want to Query Analyzer and you will have everything associated with that table ... or you can only keep the constraints. If you want more then one table then simply choose multiple tables and follow the same steps.

Hope this helps.

 

[TGM]

Hello

Thanks for your reply.
In fact, I use the import feature of the database (right-click on the db name to get it) because I'm also interested of getting the data (all those tables are heavily populated ..)

Thierry

 

[osjohnm]

Hi TGM

Since you are using DTS instead of choosing the first option, "Choose tables and views", choose the "Copy objects and data between databases".
Then you can select create objects and drop them if they exists first and independant objects etc.

You will then have an option to copy the data or not.

By default "copy all objects" is selected, if you deselect it then you can click on "select objects" where you can choose what you don't want to copy eg. views, user-defined functions etc. You can also just select the tables you want.

"use default options" is also selected by default, again you can change those options, now this is where you select primary keys, foreign keys, indexes, triggers.

You can copy object level permissions eg. execute on stored procs but if the user doesn't exist in your destination database it will fail, same with database users.

Usually I don't select the permissions and users and add those later if necessary just to be safe.

Hope that helps

John.