Duplicate table structures to a new database

Hi all expert,

Question as stated title above, how do I duplicate the table structure to another database whether it copies all columns, primary and foreign keys, default values, descriptions, constraints, indexes

Many thanks for the help.

regards,

Use Enterprise Manager's Generate Script Feature, and execute the Generated SQL on your target server.

|||

you can also check Database Publishing Wizard.

http://www.microsoft.com/downloads/details.aspx?familyid=56E5B1C5-BF17-42E0-A410-371A838E570A&displaylang=en

Madhu

|||

I want to done this using T-SQL. So what is the script it can be?

|||

There isn't a built-in t-sql command to script However, you can use sp_oa* and smo/dmo. You can check google for archive script.

As to just creating a new table based on the old one, you can just do this:

select top 0 *

into new_table

from old_table

Note that this method does not create constraints/dependencies on the new table.

|||

You can run a trace with profiler to see what Management Studio does to create the script, is a bit more than i expected, but if you really want to you should be able to replicate that on your own.

|||Manivannan gave you the answer. Use the Generate Script feature, it will make the script for you, and then you can re-use that script.

|||

Yes I know there is a Generate Script functions. But I want to schedule it and run on every month, therefore a pre-generated script must be prepared so that it can auto run on every month.

So, can this be done? Thanks a lot for the help.

regards

sql

DTS: How to connect to hundreds of DBs dynamically

I need to release a few triggers and procedures to hundreds of databases
with the same structures.
I try the Dynamic Property in DTS, create an "Execution Task", add a
connection property with a source type as "Query", and the query is like
"select the database name from master..sysdatabases where name not in
('master','model','temp')".
Unfortunately, the package runs only on the first DB in the query.
How can I make it run on each of all databases?
Thank you.Create a connection to the server,
create an "Execute SQL Task" with your databases query in it -
in the "Output parameters" tab of the sql task use a rowset output
then loop through the values in an ActiveX script to perform your jobs.
http://www.sqldts.com/default.aspx?298
"Jluo" <jluost1@.yahoo.com> wrote in message
news:4224B876.5000304@.yahoo.com...
> I need to release a few triggers and procedures to hundreds of databases
> with the same structures.
> I try the Dynamic Property in DTS, create an "Execution Task", add a
> connection property with a source type as "Query", and the query is like
> "select the database name from master..sysdatabases where name not in
> ('master','model','temp')".
> Unfortunately, the package runs only on the first DB in the query.
> How can I make it run on each of all databases?
> Thank you.
>