Moving tables to another SQL2008 database (including indexes, triggers, etc.)

I need to move a whole bunch (100+) of large (millions of rows) tables from one SQL2008 database to another.

I originally just used the Import/Export Wizard, but all the destination tables were missing primary and foreign keys, indexes, constraints, triggers, etc. (Identity columns were also converted to plain INTs, but I think I just missed a checkbox in the wizard.)

What's the right way to do this?

If this were just a couple of tables, I would go back to the source, script out the table definition (with all indexes, etc), then run the index creation portions of the script on the destination. But with so many tables, this seems impractical.

If there wasn't quite so much data, I could use the "Create Scripts..." wizard to script out the source, including data, but a 72m row script just doesn't seem like a good idea!

We actually did it using a lot of manual scripting in conjunction with the Import wizard, but this morning I found a better answer, courtesy of Tibor Karaszi's blog article.

Part of our frustration here was that the SQL 2000 "DTS Import/Export Wizard" actually makes this almost trivially easy by selecting "Copy Objects and Data":

This third option is the one that contains the ability to include indexes/triggers, etc:

This option was REMOVED from the SQL 2005/2008 Import Wizard. Why? No idea:

In 2005/2008, you apparently have to manually create an SSIS package in BIDS and use the Transfer SQL Server Objects Task, which contains all the same options that were in the 2000 wizard:

StackExchange Database Administrators Q#1038, answer score: 9