Post reply

Scripting tables

  • November 21, 2006 at 4:51 am

    #117806

    Hi all,

    In SQL Server 2000, you could easily script objects with the relevant supported objects (for example tables with indexes).

    I have found I can script tables in SQL Server 2005, but the process doesn't seem to script the indexes as well.

    Does anyone know how to script tables with there supported indexes.

    Cheers

    Peter Gadsby

    Peter Gadsby
    Business Intelligence Consultant
    www.hgconsult.co.uk

  • November 21, 2006 at 6:18 am

    #673174

    In SSMS, in the object explorer, right-click on the database name and select Tasks -> Generate Scripts ...

    This is equivalent to the "Generate SQL Script" wizard in SQL2000, and allows you to specify whether your script should contain drop commands, constraints, indexes etc.

  • November 21, 2006 at 6:29 am

    #673179

    Thanks Philip, I take it you can't combine drop/create in the same script? Also is there a way to make the process generate one file per table?

    Cheers

    Pete

    Peter Gadsby
    Business Intelligence Consultant
    www.hgconsult.co.uk

  • November 21, 2006 at 6:41 am

    #673182

    Yes you can do all those. Try experimenting with the wizard - you'll soon find out all the options

  • November 21, 2006 at 6:55 am

    #673188

    Hmmm I'm probably being really thick... But when I open the wizard, and I get to 'Choose Script Option'  the Script behavior shows either 'Generate CREATE statements only' or 'Generate DROP Statements only'. Even though the description of this option states that you can create a combined one!!!!

    Also when I get to Output option I get the option to Script to file, but no option to output to multiple files.

    Am I missing something?

     

    Pete

    Peter Gadsby
    Business Intelligence Consultant
    www.hgconsult.co.uk

  • November 22, 2006 at 8:06 am

    #673470

    You're not thick. I believe MS thought it would be fun to see how many DBA's they could confuse by dropping the one-file-per-object and drop-and-create-in-the-same-script options in the initial release of SSMS.

    You get the one-file-per-object and drop-and-create-in-the-same-script options back in SQL Srvr SP2 (CTP), you cant do either option unless you have SP2 installed. The download can be found here http://www.microsoft.com/sql/ctp.mspx.

  • November 22, 2006 at 8:27 am

    #673480

    To get to the most powerful script generator, right click on the DATABASE NAME, highlist TASKS and finaly choose GENERATE SCRIPTS.  This brings up a script wizard that will allow you to script objects in any permutation that we've had in the past.

  • November 22, 2006 at 8:28 am

    #673481

    Ha, nothing like reading the post first d'oh.

  • November 22, 2006 at 8:30 am

    #673485

    We've all been there .

  • November 22, 2006 at 9:09 am

    #673495

    Thanks everyone for your replies, I will install SP2 on the client machine.

    Are there any gotchas I should watch out for when installing this service pack? The database is hosting a data warehouse solution, using SSIS and connecting to Oracle via OLEDB.

    Cheers

    Pete

    Peter Gadsby
    Business Intelligence Consultant
    www.hgconsult.co.uk

  • November 22, 2006 at 9:37 am

    #673506

    Hi,

    As the service pack is status CTP ( Community Technology Preview ) Does this mean it is similier to a beta version? Therefore does that mean I shouldn't install it on a live box?

    Thanks

    Pete

    Peter Gadsby
    Business Intelligence Consultant
    www.hgconsult.co.uk

Viewing 11 posts - 1 through 10 (of 10 total)

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