I need to copy a table I already I have in a SQL server database, but I don't need the data contained in the source table. The examples I found only involve copying the data.
Here are the details:
select * into new_table from old_table where 1=0
Execute the above command programmatically.
INTO in this way. Ideally references would come over too, but this is a great first option.
SELECT TOP (0) INTO newTable FROM oldTable; But that's pretty much the same.Script out the table, change the table name in the script, run the script.
I bet with SMO you can do this no problem:
I quickly found a few interesting articles that show at least part of the solution:
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
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.
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).
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.
