locked
Script out the table schema RRS feed

  • Question

  • hello ,

    how can i create the table like this :

    create

     table ganeshtest

    as

     (select * from sourcetable)

     

    thanks.

    ----------------------one more point i want to add: source table and destination table are in different server. I do not have the linked server also. 

    

    i would have do it . if i have the linked server . by

     

     * into ganeshtest1 from dbo.Zganeshlmfr

     

     

    Select

     

    Wednesday, August 10, 2011 8:58 PM

Answers

  • Hi kiranshiva,

    >> i would like to automate that process. how do i get the table schema to .sql file.

    If you want to avoid using linked server, you may try to create a job to run a SSIS package which holds the task of transferring data from the source server to the target into a data table for automatic considering.
    Best Regards,
    Stephanie Lv

    Forum Support
    Please remember to mark the replies as answers if they help and unmark them if they provide no help. If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.
    • Marked as answer by Stephanie Lv Thursday, August 18, 2011 11:41 PM
    Thursday, August 11, 2011 4:33 AM
  • the quickest way to do it is this free tool:

     

    http://www.nobhillsoft.com/dianalite.aspx

     

    it keeps track of all your database entities, and you can get a script of anything you want in a split second (plus a whole bunch of other things...)

    • Marked as answer by Stephanie Lv Thursday, August 18, 2011 11:41 PM
    Monday, August 15, 2011 7:04 PM

All replies

  • In SSMS right click on the table name and select the script table as item.
    Tom G.
    Wednesday, August 10, 2011 9:53 PM
  • use import/export wizard
    Wednesday, August 10, 2011 10:02 PM
  • i would like to automate that process. how do i get the table schema to .sql file.

     

    Thursday, August 11, 2011 12:00 AM
  • Hi kiranshiva,

    >> i would like to automate that process. how do i get the table schema to .sql file.

    If you want to avoid using linked server, you may try to create a job to run a SSIS package which holds the task of transferring data from the source server to the target into a data table for automatic considering.
    Best Regards,
    Stephanie Lv

    Forum Support
    Please remember to mark the replies as answers if they help and unmark them if they provide no help. If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.
    • Marked as answer by Stephanie Lv Thursday, August 18, 2011 11:41 PM
    Thursday, August 11, 2011 4:33 AM
  • can you please provide more details on it. how can i do it?
    Thursday, August 11, 2011 5:22 AM
  •  QQ: are you referring to transfer sql object task in SSIS .

    Thursday, August 11, 2011 5:37 AM
  • Hi kiranshiva,

    >> QQ: are you referring to transfer sql object task in SSIS .
    It depends on your requirement. It is available to transfer a table object between SQL Server instances with SSIS. If you want to specify the target table on the target server, then you can create a Data Flow Task with two data sources components (source and destination) to transfer data between them. Also you can add an Execute SQL Task to clear the existing records in the target table before the Data Flow Task to keep the data records as the same as the sources table each time.


    Best Regards,
    Stephanie Lv

    Forum Support
    Please remember to mark the replies as answers if they help and unmark them if they provide no help. If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.
    Thursday, August 11, 2011 6:49 AM
  • the quickest way to do it is this free tool:

     

    http://www.nobhillsoft.com/dianalite.aspx

     

    it keeps track of all your database entities, and you can get a script of anything you want in a split second (plus a whole bunch of other things...)

    • Marked as answer by Stephanie Lv Thursday, August 18, 2011 11:41 PM
    Monday, August 15, 2011 7:04 PM