How To Copy Table Schema In SQL Server

As an SQL developer, I must constantly duplicate the table structure without copying the data. This is where copying the table schema comes into the picture. Since this process is part of my daily activity, I have identified a few approaches to copying the table schema in SQL Server. In this article, I will walk you through all the approaches individually.

Approach-1: Using the SELECT INTO Statement

This method helps us create a new table with the same structure without copying the data.

Syntax

SELECT TOP 0 * INTO NewTable
FROM OldATable
WHERE 1 = 0

Example

The below query will create a new table named NewOrder with the same table structure as Order.

SELECT TOP 0 * INTO NewOrder
FROM [Order]
WHERE 1 = 0;

After executing the above query, I got the expected output below.

Now, to cross-check if the table was created successfully, we can execute the Select query below. You can see that I got the expected output, as shown below.

Check out How to Export SQL Server Table to CSV

Approach-2: Generating CREATE TABLE Script using SSMS

Follow the below steps.

1. Log in to SSMS.

2. Right-click on the table name and then select Script table as –> Create to –> New query editor window, as shown in the screenshot below.

3. Modify the table name and execute the query as shown in the screenshot below.

USE [Test]
GO

/****** Object:  Table [dbo].[Order]    Script Date: 29-10-2024 13:12:48 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[CloneOrder](
	[OrderName] [nvarchar](50) NOT NULL,
	[OrderType] [nvarchar](50) NULL,
	[OrderID] [int] NULL,
	[ProductID] [int] NULL,
	[TotalAmount] [int] NULL
) ON [PRIMARY]
GO

The table was created successfully, as shown in the screenshot below.

Check out How To Duplicate A Table In SQL Server

Approach-3: DBCC CLONEDATABASE statement

This command helps you create a new database with all the table structures from the original database without any data.

Syntax

DBCC CLONEDATABASE (OldDBName, NewDBName)

Example

We can execute the below query to create the Test_Schema database, which is the clone of the Test database.

DBCC CLONEDATABASE (Test, Test_Schema)

After executing the above query, I got the expected output below.

Conclusion

It is easy to duplicate a table in SQL Server when you know the steps to copy table schema in SQL Server using the above information.

You may also like following the articles below.

• How To Check User Permissions On Table In SQL Server
• How To Find Table With Column Name in SQL Server
• How To Check If Column Exists In SQL Server Database