I have to copy a table I already I've inside a SQL server database, however i have no need for th edata included in the source table. The good examples I discovered only involve copying the information.

  1. Copy the table structure only, not the information.
  2. Source and target tables have been in exactly the same database.
  3. Target table doesn't exist yet.
  4. Have to do this programmatically
  5. It might be nice to possess any related qualities on the tableOrposts be replicated too.

Thanks!

choose * into new_table from old_table where 1=

Execute the above mentioned command programmatically.

Script the table, alter the table title within the script, run the script.

I wager with SMO this can be done not a problem:

  • browse the structure of the "old" table into in-memory variables
  • make use of this structure info to produce the brand new table

I rapidly found a couple of interesting articles that demonstrate a minimum of area of the solution:

So essentially it might boil lower to something similar to 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();

Obviously, you will find more qualities around the "Column" which you might like to copy over, plus you could also wish to copy indices, constraints etc. - work.

I am stumped there is not an simpler method to duplicate a "Column" object to a different one (something similar to b .Duplicate() method) to help ease this - it can be not really a top-priority scenario, I'm not sure....

Hope this can help!

Marc

In SQL Management Studio, right click the table title and Script Table as CREATE TO ... New Query editor Window. That provides you with a script that you could run under any database.

If you are using .Internet, you should 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 have to replace what they are called on the table and connected objects (e.g. FK_OldTableName_xxx with FK_NewTableName_xxx) within the produced script:

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

After which execute:

db.Execute(sb.ToString());

Observe that this really is pretty naive code: it'll only work when the names of the constraints and secrets stick to the format: FK_OldTableName_xxx / CK_OldTableName_xxx .. should they have other names, you'd have to strengthen the string alternative code, most likely using Regexes to search for T-SQL object creation designs (i.e. CREATE INDEX, FOREIGN KEY .. etc).

Must you do that instantly (without manual interference)?

Otherwise, you could use SQL Server Management Studio to create CREATE scripts for that tables you need to copy and run it around the target database.

Right click the object you would like, pick generate script, and select your script to create.