SQL to duplicate table structure with / without data rows

Just a note to myself.

How to duplicate a table structure with / without data. Drop the where 1=0 to copy over the data as well.

As an example the Oracle HR scheme with the employees table is used and replicated to employees2 (see bold SQL line below).

Note, that not null constraints are replicated but primary keys, foreign key, unique constraints and triggers aren’t.

SQL> describe employees;
 Name Null? Type
 ----------------------------------------- -------- ----------------------------
 EMPLOYEE_ID NOT NULL NUMBER(6)
 FIRST_NAME VARCHAR2(20)
 LAST_NAME NOT NULL VARCHAR2(25)
 EMAIL NOT NULL VARCHAR2(25)
 PHONE_NUMBER VARCHAR2(20)
 HIRE_DATE NOT NULL DATE
 JOB_ID NOT NULL VARCHAR2(10)
 SALARY NUMBER(8,2)
 COMMISSION_PCT NUMBER(2,2)
 MANAGER_ID NUMBER(6)
 DEPARTMENT_ID NUMBER(4)

SQL> 
SQL> create table employees2 as select * from employees where 1=0;

Table created.

SQL> describe employees2;
 Name Null? Type
 ----------------------------------------- -------- ----------------------------
 EMPLOYEE_ID NUMBER(6)
 FIRST_NAME VARCHAR2(20)
 LAST_NAME NOT NULL VARCHAR2(25)
 EMAIL NOT NULL VARCHAR2(25)
 PHONE_NUMBER VARCHAR2(20)
 HIRE_DATE NOT NULL DATE
 JOB_ID NOT NULL VARCHAR2(10)
 SALARY NUMBER(8,2)
 COMMISSION_PCT NUMBER(2,2)
 MANAGER_ID NUMBER(6)
 DEPARTMENT_ID NUMBER(4)