Making a copy of a table.

Question

Making a copy of a table.

ben.brugman

SSChampion

Points: 13369
More actions
May 28, 2013 at 7:47 am

#273127

What is a good method to make a copy of a table.
Only the table not the data.
Including all indexes.
Excluding referential constraints.
Including other (domain) constraints.
At the moment I generate the table, change the name of the table to the new name and use the generated script.
Problem the indexes have the 'old' name.
Not all indexes include the name of the table so only substituting the name of the table is not enough.
Any nice ways to make a empty copy of a table including indexes ?
Thanks for you time and attention.
Ben Brugman

Viewing 15 posts - 1 through 15 (of 17 total)

You must be logged in to reply to this topic. Login to reply

Andre Guerreiro SSCertifiable Points: 7319 More actions · Answer 1

I'm thinking of the Generate Scripts Wizard feature here.

Couldn't you do a smart replacing in a text editor to change the part of the index's name the mentions the table's name? I don't see any other way as object names are generated by the user and he's responsible for making them readable and unique.

[EDIT] And to those that don't have the table's name can't you just add it to the name?

Best regards,

Andre Guerreiro Neto

Database Analyst
http://www.softplan.com.br
MCITPx1/MCTSx2/MCSE/MCSA

ben.brugman SSChampion Points: 13369 More actions · Answer 2

codebyo (5/28/2013)
I'm thinking of the Generate Scripts Wizard feature here.
Couldn't you do a smart replacing in a text editor to change the part of the index's name the mentions the table's name? I don't see any other way as object names are generated by the user and he's responsible for making them readable and unique.
[EDIT] And to those that don't have the table's name can't you just add it to the name?

I started of with the Wizard and global substitutes.

And I made a mess of it.

(Substitute of the tablename, messed up previous code, some fields contained the tablename in full, some tablenames are parts of other tables, and the indexes did not change).

So I thought before I come up with a fancy method and spend some time on that, lets first ask if others have a better solution.

Best solution seems to be to alter the code by hand.

Thanks for your contribution,

Ben Brugman

Evgeny Garaev SSCertifiable Points: 7428 More actions · Answer 3

You can start from:

select * into dbo.newtable

from dbo.oldtable

if you need new table to be populated with old table data, then you can add required constraints and indexes.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

Can you post the requirements of what you are trying to accomplish? I sort of understand that you want to make a copy of the table and all of it's structures except DRI constraints, I would just like this confirmed with a set of requirements.

ben.brugman SSChampion Points: 13369 More actions · Answer 5

Lynn Pettis (5/29/2013)
Can you post the requirements of what you are trying to accomplish? I sort of understand that you want to make a copy of the table and all of it's structures except DRI constraints, I would just like this confirmed with a set of requirements.

To do some batch tests. Timing, bulk insert, forwarded records etc.

I want to duplicate a table as close as possible to the original, so with all indexes included.

In this case I want to start off with an empty table as close as possible to the original. (I could empty the original, but do not want to do that).

I am allready working with three databases. One to hold the data in and two to insert the data in. And I am comparing different structures in the two databases. The databases are large and I want to run a lot of tests, so for me it's not oppertune to have more copies of the database.

Constraints (referential) should be included as well but this would make the test to complex for the current situation.

Thanks for asking,

Ben Brugman

ben.brugman SSChampion Points: 13369 More actions · Answer 6

Evgeny (5/29/2013)
You can start from:
select * into dbo.newtable
from dbo.oldtable
if you need new table to be populated with old table data, then you can add required constraints and indexes.

I do not want to copy the content. (Add a Where 1 = 2 is possible).

But this structure does not copy the indexes.

The problem is the indexes, using the scripting wizard supplies the indexes. But the names of the indexes have to be altered if used in the same database, also the name of the table.

Thanks,

Ben

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 7

...
Any nice ways to make a empty copy of a table including indexes ?
...

There is one I know.

Develop your own utility to do so.

Here is the link where you can pick up code for scripting table body:

http://stackoverflow.com/questions/12308716/script-table-schema-using-tsql

You will need to fix it as it places one extra comma at the end of column list and you will need to add "GO" and NewLine characters to separate CREATE TABLE from ALTER one or script PK as part of CREATE TABLE.

You will need to custom-build script (or google it out) to generate script for indexes.

When all above is done, you will need apply global REPLACE on generated SQL to rename table, constraints and indexes.

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

Lowell SSC Guru Points: 323518 More actions · Answer 8

well if you want to do it via TSQL, look at my script contribution from a while ago;

a couple of procedure sp_GetDDL and sp_getDDLa which scripts any object: table,#temp or ##temp table definition including indexes, or the definition of procedures/functions/views:

the only difference is the format of the results: a multi line result set or a varchar(max) string.

http://www.sqlservercentral.com/Forums/Topic751783-566-5.aspx

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 9

Eugene Elutin (5/29/2013)
...
Any nice ways to make a empty copy of a table including indexes ?
...
There is one I know.
Develop your own utility to do so.
Here is the link where you can pick up code for scripting table body:
http://stackoverflow.com/questions/12308716/script-table-schema-using-tsql
You will need to fix it as it places one extra comma at the end of column list and you will need to add "GO" and NewLine characters to separate CREATE TABLE from ALTER one or script PK as part of CREATE TABLE.
You will need to custom-build script (or google it out) to generate script for indexes.
When all above is done, you will need apply global REPLACE on generated SQL to rename table, constraints and indexes.

Took a quick look at it, don't like it as it uses the INFORMATION_SCHEMA views. The MS SQL system views provide much more information that is valuable to fully script out a table. This is doable but you are looking at several hours of work, more if you are familiar with writing dynamic sql and/or the system views needed to accomplish the task.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 10

Lowell (5/29/2013)
well if you want to do it via TSQL, look at my script contribution from a while ago;
a couple of procedure sp_GetDDL and sp_getDDLa which scripts any object: table,#temp or ##temp table definition including indexes, or the definition of procedures/functions/views:
the only difference is the format of the results: a multi line result set or a varchar(max) string.
http://www.sqlservercentral.com/Forums/Topic751783-566-5.aspx

Now this is something worth looking at. For purposes here I would think it would need tweaking to take an existing table and create a new table, but definately doable from here.

Evgeny Garaev SSCertifiable Points: 7428 More actions · Answer 11

ben.brugman (5/29/2013)
Evgeny (5/29/2013)
You can start from:
select * into dbo.newtable
from dbo.oldtable
if you need new table to be populated with old table data, then you can add required constraints and indexes.
I do not want to copy the content. (Add a Where 1 = 2 is possible).
But this structure does not copy the indexes.
The problem is the indexes, using the scripting wizard supplies the indexes. But the names of the indexes have to be altered if used in the same database, also the name of the table.
Thanks,
Ben

It looks like you want some database, which is cleaned from sensitive data, for testing purposes. Don't you?

For this purpose I employed backup/restore and truncation of transaction tables, but I do not truncate some dictionaries tables. Then I just shrink that database and handover it to the testing team.

If it is what you need, I can share my scripts. Do you need it?

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 12

...
Took a quick look at it, don't like it as it uses the INFORMATION_SCHEMA views. The MS SQL system views provide much more information that is valuable to fully script out a table. This is doable but you are looking at several hours of work, more if you are familiar with writing dynamic sql and/or the system views needed to accomplish the task.

I didn't even looked so far!

I have just tried it seen, extra comma and left it.

You are right about time which would need to be spent on developing and testing it.

T-SQL is not the best tool for doing so.

If you are serious about it, then it should be done in application language using SQL Server SMO.

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 13

Eugene Elutin (5/29/2013)
...
Took a quick look at it, don't like it as it uses the INFORMATION_SCHEMA views. The MS SQL system views provide much more information that is valuable to fully script out a table. This is doable but you are looking at several hours of work, more if you are familiar with writing dynamic sql and/or the system views needed to accomplish the task.
I didn't even looked so far!
I have just tried it seen, extra comma and left it.
You are right about time which would need to be spent on developing and testing it.
T-SQL is not the best tool for doing so.
If you are serious about it, then it should be done in application language using SQL Server SMO.

If you aren't familiar with writing code using SMO it cold take even longer to write, test, and debug. Using T-SQL isn't necessarily wrong, just something you have to be very comfortable working with.

sturner SSC-Insane Points: 22411 More actions · Answer 14

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.

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