Making a copy of a table.

• Lynn Pettis

  SSC Guru

  Points: 442447

  More actions

  May 29, 2013 at 11:06 am

  #1619417

  sturner (5/29/2013)

  ---

  I actually wrote my own CopySQLTable utility. It is a command line utility and works (syntax-wise) very much like the good ol' windows copy command. You can copy from one table, in one database on a DB server to another table, another database on another server and can even rename the table (and all the contraints) along the way.

  In essence, what it does is script out the table using SQLServer SMO classes (renaming table and constraints if destination table name different than source), create the table on destination, then native mopde BCP the table data out to a file and BCP it back in to the destination table. It is very fast copying very large tables because of the BCP. It depends on Windows security and of course the windows account you are running the utility as needs to have the permissions to create the destination table and do the BCP insert.

  Can it skip the data transfer, the OP only wants the schema with no data.

• sturner

  SSC-Insane

  Points: 22411

  More actions

  May 29, 2013 at 11:13 am

  #1619421

  Lynn Pettis (5/29/2013)

  ---

  Can it skip the data transfer, the OP only wants the schema with no data.

  Actually I have a standalone table scripting .net utility that will script out a table (and rename it if requested) which would probably be what he's looking for. It has a number of command line options and can even script out Stored procedures and SQL Agent jobs (which feature I use on all my prod servers to replicate agent jobs to DR sites).

  I could make the source available, it is written in C# with .net 3.5 runtime (IIRC).

  The probability of survival is inversely proportional to the angle of arrival.

• ben.brugman

  SSChampion

  Points: 13369

  More actions

  May 30, 2013 at 9:37 am

  #1619763

  sturner (5/29/2013)

  I could make the source available, it is written in C# with .net 3.5 runtime (IIRC).

  Thanks for the offer, I am not proficient enough with C#.

  Problem with the wizard and renaming the indexes it takes some time and effort, so I asked in the group if somebody had a readymade solution. But at the moment my conclusion is that this is the fasted way.

  It's better to ask for something and learning that it is not available, than first spending time and then learning that there was a 'tool' to do the same in less time.

  Everybody thanks for your contributions,

  Ben