none
Copy table Structure including primary keys, index etc.

    Question

  •  

    Hi all,

    I was wondering if there is a SQL command to copy the table structure of a table that includes primary keys, foreign keys, indexes, etc.

     

    Thanks and have a nice day to all

     

    Thursday, January 18, 2007 4:49 PM

Answers

  •  

    SELECT * INTO NewEmployee  FROM Employee WHERE 1 = 0

     

    above query will create same structure table called NewEmployee with structure of Employee. But will not have triggers and primary keys etc. you can create them by using follwing scripts

    SELECT *

    FROM sysobjects

    WHERE parent_obj = OBJECT_ID('Employee')

    SELECT *

    FROM syscomments

    WHERE id IN ( SELECT id

    FROM sysobjects

    WHERE parent_obj = OBJECT_ID('Employee') )

    Friday, January 19, 2007 7:51 AM

All replies

  • Not a SQL command, but you can script this stuff out using the tools by right clicking the table, or programatically using SMO. 

     

    Thursday, January 18, 2007 4:56 PM
  • can you post a sample script  or SMO please  or send me a link discuss this matter thanks
    Thursday, January 18, 2007 5:25 PM
  • Hi,

    The easiest way to create the script is to right click the original table within SQL Server Management Studio and select "Script Table As ...\ Create To\ ..." . This will create a script for the table and its indexes.

    Then you need to run the script but with the new tablename. After that, you need to copy the records using a insert/select command. (it is best to set the constraints/indexes afterwards).

    Greetz,

    Geert

     

    Geert Verhoeven
    Consultant @ Ausy Belgium

    My Personal Blog

    Thursday, January 18, 2007 9:19 PM
  • I was thinking to use that script in my SP, On my SP I add a linked server then i want to copy all the tables exactly the same

    and on the linked server the table names and table count change everyday, but i dont have any problem with that.

     I was thinking if there is a way to copy exactly the same table inside an SP in that case.

     

    Thanks

    Thursday, January 18, 2007 10:23 PM
  • You can use sys tables/views inside your SP
    Thursday, January 18, 2007 10:31 PM
  • can you post your samples script please.

    thanks

    Friday, January 19, 2007 12:34 AM
  •  

    SELECT * INTO NewEmployee  FROM Employee WHERE 1 = 0

     

    above query will create same structure table called NewEmployee with structure of Employee. But will not have triggers and primary keys etc. you can create them by using follwing scripts

    SELECT *

    FROM sysobjects

    WHERE parent_obj = OBJECT_ID('Employee')

    SELECT *

    FROM syscomments

    WHERE id IN ( SELECT id

    FROM sysobjects

    WHERE parent_obj = OBJECT_ID('Employee') )

    Friday, January 19, 2007 7:51 AM
  • thanks for the reply Dinesh. Nice sql Stmt, does this work when you have a MS Access linked Server, is there a sysobjects table on the linked server?
    Friday, January 19, 2007 5:08 PM