How to create duplicate table with new name in SQL Server 2008

How do I create a duplicate table with only the structure duplicated with a new name in SQL server 2008?

I have table with 45 fields so I want to create new with same structure but new name.


Right click on the table in SQL Management Studio.

Select Script... Create to... New Query Window.

This will generate a script to recreate the table in a new query window.

Change the name of the table in the script to whatever you want the new table to be named.

Execute the script.


SELECT * 
INTO target
FROM  source
WHERE 1 = 2

Here, I will show you 2 different implementation:

First:

If you just need to create a duplicate table then just run the command:

SELECT top 0 * INTO [dbo].[DuplicateTable]
FROM [dbo].[MainTable]

Of course, it doesn't work completely. constraints don't get copied, nor do primary keys, or default values. The command only creates a new table with the same column structure and if you want to insert data into the new table.

Second (recommended):

But If you want to duplicate the table with all its constraints & keys follows this below steps:

  1. Open the database in SQL Management Studio.
  2. Right-click on the table that you want to duplicate.
  3. Select Script Table as -> Create to -> New Query Editor Window. This will generate a script to recreate the table in a new query window.
  4. Change the table name and relative keys & constraints in the script.
  5. Execute the script.