Hi,
Anybody know how to copy the table with constraints (including primary key, foreign key, index, etc..) ???
I used the following copy statement and it does copy ONLY the table with records.
Select *
Into T1
From T2
Any idea?
Thanks.
Scripting of constraints is not an easy thing - there are several products available to do it - my schema comparison tool, Red Gate SQL Compare, Apex SQL Diff.
If you want to script out the constraints using T-SQL only, I'm doing a blog series on metadata at the moment.
Your other option is to use Powershell / C# / VB.NET and SMO.
None of them are particularly straightforward.
There are two types of command in T-SQL, DDL and DML.
DDL - Data Definition Language is the description of the objects in a database - Tables, Schemas, Procedures and Functions etc
DML - Data Management Language is the script that manipulate and queries the data in database tables.
SELECT * INTO Tab_Dest FROM Tab_Source is a piece of DML code that manages to create a table but only enough of the table to hold the data, it doesnt inspect the source table for any features.
In order to get the new table identical you will need to use a DDL script that creates the table and then use INSERT INTO ... SELECT * FROM ... to transfer the data.
