How to copy a table definition in SQL?

Department Table

deptno dname loc
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON

You want to create a new table having the same set of columns as an existing table. For example, you want to create a copy of the Department table and call it Department _2. You do not want to copy the rows, only the column structure of the table.

DB2

Use the LIKE clause with the CREATE TABLE command:

CREATE TABLE  Department _2 LIKE Department 

Oracle, MySQL, and PostgreSQL

Use the CREATE TABLE command with a subquery that returns no rows:

CREATE TABLE Department _2
AS SELECT  *
FROM Department 
WHERE 1 = 0

SQL Server

Use the INTO clause with a subquery that returns no rows:

SELECT *
INTO Department _2
FROM  Department
WHERE 1 = 0

Discussion

DB2

DB2’s CREATE TABLE…LIKE command allows you to easily use one table as the pattern for creating another. Simply specify your pattern table’s name following the LIKE keyword.

Oracle, MySQL, and PostgreSQL

When using Create Table As Select (CTAS), all rows from your query will be used to populate the new table you are creating unless you specify a false condition in the WHERE clause. In the solution provided, the expression 1 = 0 in the WHERE clause of the query causes no rows to be returned. Thus, the result of the CTAS statement is an empty table based on the columns in the SELECT clause of the query.

SQL Server

When using INTO to copy a table, all rows from your query will be used to populate the new table you are creating unless you specify a false condition in the WHERE clause of your query. In the solution provided, the expression 1 = 0 in the predicate of the query causes no rows to be returned. The result is an empty table based on the columns in the SELECT clause of the query.