How to copy schema,data,objects,indexes, constraints from one database into another in MSSQL Server

In SQL Server you can easily copy values as well as schema/structure from one table to another using following commands

select * into db1.dbo.<destination table> from db2.dbo.<source table>

The limitation of this method is that, only copies the table schema , data. and leave the objects, indexes, triggers or constraints behind.

For example in the above command will copy columns to the destination table and leave identity column as regular, your new table will not hold any identity columns .

Solution

Solution to this problem is generate the scripts using Script Wizard and then execute the script to generate all objects and indexes

• Right Click the database in your SQL Server Management Studio then choose Tasks- Generate scripts, choose a database then select objects type and review and wait for script.
• Once it was generated go head choose and execute then script

All of the table structure,index,triggers,constraints will be created for your new database.

Now you are ready to copy data from one database from another using following insert command

insert into <destination table> (col1,col2 ...) 
select  col1,col2...  from fin_app_data.dbo.groups

Please leave identity column behind.