How to create new table with same constraints and indexes?

Question

I am Creating a new table with Primary Key Constraints and a Non Clustered Index in that table.

I know, I would like to create another table with same structure and values as well keys and indexes also.

create table Dummy (id integer ,name varchar(20),salary integer
Constraint PK_Con_id primary key(id))

insert into Dummy values(11,'AAA',1000);
insert into Dummy values(12,'BBB',2000);
insert into Dummy values(13,'CCC',3000);
insert into Dummy values(14,'DDD',4000);

select * from Dummy;

create nonclustered index IX_Name 
on Dummy(Name)

Now I am creating Dmy table but Keys and Constraints not reflect in Dmy table in SQL Server 2008 R2.

SELECT * 
INTO Dmy 
FROM Dummy

Answer 1

SELECT INTO is not going to do this for you, because while it maintains the column names and data types, it doesn't keep a lot of the other aspects of the table, such as constraints, indexes, etc. The only thing it really keeps outside of the columns is the IDENTITY property if one of the columns has it.

Right-click the original table in Object Explorer, and select Script Table As > Create To > New Query Window. You should have a CREATE TABLE script for the original table, including all the constraints, etc. Note that you may have to set some of these settings (Tools > Options > SQL Server Object Explorer > Scripting) to true in order to get all of the table attributes you want, so this may take some trial and error:

Now, just hand-modify that script to specify the new table name, and make sure to adjust all of the constraint and index names such that they are unique (how much work this will be will depend on whether you have an established naming convention or if you just take what SQL Server will throw at you).

Answer 2

All can be done in SQL Server Management Studio.

The Script Table As option generates create code for primary and foreign keys and constraints. It doesn't create script for the indexes, so you have to do that in another step. type, I suggest expanding all object type nodes in SSMS Object Explorer.

To create a script for table structure, primary and foreign keys, and constraints

1. Run SQL Server Management Studio
2. In Object Explorer, expand Databases
3. Expand Tables
4. Right click the table and select Script table as | CREATE TO | New Query Editor Window

The generated script will be shown in the Query Editor tab. Make sure you change the table, primary and foreign key , and constraint names, as these names have to be unique.

Otherwise, you'll get an error message saying something like 'There is already an object named 'Address' in the database.', or 'The operation failed because an index or statistics with name 'AK_Address_rowguid' already exists on table 'Person.Address'.'

To create a script for an index

Use the steps similar to the above:

To create an INSERT INTO script for data

1. Right-click the database (not the table!)
2. Open Tasks | Generate Scripts
3. On the Choose Objects tab, select the table to script

4.In the Set Scripting Options tab, click Advanced and make sure the Types of data to script option is set to Data only

Note: If you select Schema & Data the generated schema script will be identical to the script generated in the first method in this answer.

5.Update the table name to the name of the new table before you execute the script

Answer 3

FOR Schema- Table, Constraints and Keys

• In Object Explorer, select TABLE (say ABC) > Right Click on it
• Then select Script table as > CREATE TO > New Query Editor Window
• Table Create script for ABC will open up
• Change Table Name
• Change PK
• Change Constraint name
• Change Index Name
• Now execute this script, Table will be created with same structure but with different name...

FOR Index

• Select Table ABC > Expand it
• Select INDEX > Right click on the NON CLUSTERED INDEX
• Script Index AS > Create TO > New Query Editor Window
• Change index name
• Execute on the newly created table for creating INDEX on it.....

FOR Data

• Select Database Name
• Right Click on it
• Select Task > Generate Scripts...
• A new window will open up
• Select Next
• Now select Specific database objects and Select Table ABC
• Select Next
• Go in Advance >
• Make Type in data to script as DATA ONLY
• Then OK > Next > Next > Finish....
• Now Execute this script in newly created table