Copying table structure in MS SQL 2005

    The task is to have a table, you need to create a copy of it ... or an empty table with the same structure ...

    Previously, I usually used DTS for these purposes, because the need for copying was rare and usually related to one table. It didn't break me to run Enterprise Manager / Management Studio.

    Today it became necessary to copy tables ... many, different, and without going beyond the scope of the program being developed ... i.e. DTS does not offer, and generally it is advisable to do just T-SQL.

    "Oh mom, it is necessary now to get the original name of the table to tear off her create statement, not to forget about the constraints and foreign keys ... :(» - I thought.

    And then, miraculously found a way to copy the structure of the table in a single line:

    select * into [destinationTable] from [sourceTable] where 0 = 1

    If the table [destinationTable] does not exist, then it will be created! With the same columns, indices and more. True, foreign keys will be lost, but now you will have to dance with a tambourine much less: D

    ZY: maybe it's a dupe, but I didn’t know about this select..into behavior: ")

    Also popular now: