How to programmatically copy a table on SQL Server without data?

Question

I need to copy the table that I already have in the SQL server database, but I do not need the data contained in the source table. The examples I found include only copying data.

Here are the details:

• Copy the structure of the table, not the data.
• Source and destination tables are in the same database.
• The target table does not yet exist.
• Need to do this programmatically
• It would be nice to copy any related table / column properties.

Answer 1

select * in new_table from old_table, where 1 = 0

Run the above command programmatically.

Answer 2

Script from the table, change the name of the table in the script, run the script.

Answer 3

I bet with SMO, you can do it without problems:

• read the structure of your "old" table into variables in memory.
• use this structure information to create a new table

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

• http://www.sqlservercentral.com/articles/SMO/scriptdatabaseobjectswithsmo/2342/

So basically it would come 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 in the Column that you can copy, plus you can also copy indexes, restrictions, etc. - extra work.

I'm at a dead end that there is no easier way to duplicate the Column object to a new one (something like the .Clone () method) to facilitate this - maybe this is not a priority scenario, I don’t know ....

Hope this helps!

Mark

Answer 4

In SQL Management Studio, right-click the table name and Script Table as | CREATE TO ... | New request editor window. This will give you a Script that you can run in any database.

Answer 5

If you use .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 will need to replace the table names and related 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 do:

 db.Execute(sb.ToString()); 

Please note that this is pretty naive code: it will only work if the names of your constraints and keys match the format: FK_OldTableName_xxx / CK_OldTableName_xxx .. If they have other names, you need to strengthen the string replacement code, probably using Regexes for Search for T-SQL creation patterns (for example, CREATE INDEX, FOREIGN KEY .., etc.).

Answer 6

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 them in the target database.

Right click on the object you want, select generate script and select script to generate.