How to programmatically copy a table in SQL Server, without the data?

Question

I need to copy a table I already I have in a SQL server database, but I don't need th edata contained in the source table. The examples I found only involve copying the data.

1. Copy the table structure only, not the data.
2. Source and target tables are in the same database.
3. Target table does not exist yet.
4. Need to do this programmatically
5. It would be nice to have any related properties of the table/columns be copied as well.

Thanks!

Answer 1

Script out the table, change the table name in the script, run the script.

Answer 2

In SQL Management Studio, right click on the table name and Script Table as | CREATE TO ... | New Query editor Window. That will give you a script that you can run under any database.

Answer 3

Do you need to do this automatically (without manual interference)?

Otherwise, you can always use SQL Server Management Studio to generate CREATE scripts for the tables you want to copy and run it on the target database.

Right click on the object you want, pick generate script, and choose your script to generate.

Answer 4

select * into new_table from old_table where 1=0

Execute the above command programmatically.

Answer 5

I bet with SMO you can do this no problem:

• read the structure of your "old" table into in-memory variables
• use this structure info to create the new table

I quickly found a few interesting articles that show at least part of the solution:

• http://www.davidhayden.com/blog/dave/archive/2006/01/27/2775.aspx
• http://www.sqlservercentral.com/articles/SMO/scriptdatabaseobjectswithsmo/2342/

So basically it would boil down to something like this:

Server localServer = new Server("(local)");
Database testDB = localServer.Databases["test"];

Table myTable = testDB.Tables["TestFiles"];
myTable.Refresh();

Table newTable = new Table(testDB, "MyNewTableName");

foreach(Column col in myTable.Columns)
{
    Column newColumn = new Column(newTable, col.Name);

    newColumn.DataType = col.DataType;
    newColumn.Default = col.Default;
    newColumn.Identity = col.Identity;
    newColumn.IdentityIncrement = col.IdentityIncrement;
    newColumn.IdentitySeed = col.IdentitySeed;
    newColumn.Nullable = col.Nullable;

    newTable.Columns.Add(newColumn);
}

newTable.Create();

Of course, there are more properties on the "Column" which you might want to copy over, plus you might also want to copy indices, constraints etc. - extra work.

I'm stumped that there isn't an easier way to duplicate a "Column" object to a new one (something like a .Clone() method) to ease this - maybe it's not a top-priority scenario, I don't know....

Hope this helps!

Marc

Answer 6

If you're using .NET, you can use Server Management Objects:

var so = new ScriptingOptions();
so.Triggers = true;
so.DriForeignKeys = true;
so.DriDefaults = true;
so.DriAllConstraints = true;
so.DriAllKeys = true;
so.DriIndexes = true;
so.DriUniqueKeys = true;
so.DriPrimaryKey = true;
so.Indexes = true;
so.Default = true;
so.ClusteredIndexes = true;
so.IncludeDatabaseContext = true;
so.TargetServerVersion = SqlServerVersion.Version90;

var server = new Server("ServerName");
var db = server.Databases["DatabaseName"];
var stringColl = db.Tables["Table"].Script(so);

You'd need to replace the names of the table and associated objects (e.g. FK_OldTableName_xxx with FK_NewTableName_xxx) in the generated script:

var sb = new StringBuilder();
foreach(var s in stringColl)
{
    var r = s.Replace("OldTableName", "NewTableName");
    sb.AppendLine(r);
}

And then execute:

db.Execute(sb.ToString());

Note that this is pretty naive code: it will only work if the names of your constraints and keys follow the format: FK_OldTableName_xxx / CK_OldTableName_xxx .. if they have other names, you'd need to beef up the string replacement code, probably using Regexes to look for T-SQL object creation patterns (i.e. CREATE INDEX, FOREIGN KEY .. etc).