How to create table NEW_TABLE "like" OLD_TABLE including all column constraint definitions?

Question

Hi All,

I am trying to create a copy of an existing table into a new table and retain all of it's old column definitions including all defined constraints. The table has over 200 columns, and typing in each constraint definition is tedious. I don't see a way in SQL Server to accomplish this without doing it one-by-one. What am I missing?

if (exists(select * from sysobjects where name = 'old_table' and type = 'U')) drop table old_table;

create table old_table
(
    a       char(1) not null default 'Y',
    b       integer not null default 0,
    c       datetime not null default '2020-01-01 00:00:00',
    d       integer null
);

insert  into old_table (d) values (500), (null);
select * from old_table;
exec sp_columns old_table;

I want to create new_table from the definition of old_table. I want it to include all the constraints defined by old_table.

When I try and script the CREATE statement using SSMS, column constraints are not included. I could manually type each constraint definition, but I'm hoping there is an automated way of doing this.

Your help is appreciated!

Thank you.

--Dan

Answer 1

Right click on your database, select Tasks and then Generate Scripts .... On the Choose Objects window, check "Select specific database objects" and then select the table you want to script. On the "Set Scripting Options" window, click on the Advanced button and then you can select any options you want, i.e., Script Indexes .... After the script is generated, replace the table name and constraint names (hope you have a good name convention on the objects). And then you can add the new columns in the script.

Answer 2

You can control what is included with scripting under Tools->Options->SQL Server Object Explorer->Scripting.

By default all types of constraints are included when you script a table, though. However, beware that SSMS does not include the constraints with the CREATE TABLE statement, but adds them separately with ALTER TABLE statements.

Answer 3

Erland/GuoxiongYuan

Thank you very much. A bit buried, but I figured there had to be a way to do this.

I have been scratching my head over why something so obvious wasn't apparent!