Post reply

Script to generate create table ddl

  • July 29, 2009 at 12:46 pm

    #385080

    Hi,

    I would like to generate the DDL of all my tables in a batch process to replicate the table on another server/databases. I also need to move all indexes.

    How can this be done?

    Does someone can provide me some help?

    I am also open to any suggestions. I want to get ride of the SSIS copy objects.

    Thanks

  • July 29, 2009 at 1:31 pm

    #1031993

    Right click database -> tasks -> Generate scripts -> "Choose Script Options" Set "Script Indexes " to TRUE

    Edit: maybe you meant as a scheduled job ? Then ignore above.

  • July 29, 2009 at 1:45 pm

    #1032001

    Scripting of objects can be done through SMO. But I have not tried.

  • July 29, 2009 at 4:49 pm

    #1032071

    I was stubborn and decided i had to do this the hard way via T-SQL;

    take a look at my script contributions here:

    Get DDL for Any SQL 2000 Table

    Get DDL for Any SQL 2005 Table

    I've also got an article coming up on how to script your whole database via TSQL; procs, procedures, tables, all in FK hierarchy order. doesn't use SMO or any outside process, simply grabs it from the metadata like sys.objects and all that.

    that's not out yet, but I threw it on a web page here

    you end up calling a procedure sp_export_all, and it creates a table with all the DDL and everything. from there you can save it to another table, bcp out or whatever.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Viewing 4 posts - 1 through 3 (of 3 total)

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