206
CREATE TABLE foo SELECT * FROM bar

copies the table foo and duplicates it as a new table called bar.

How can I copy the schema of foo to a new table called bar without copying over the data as well?

ChrisGPT was on strike
  • 127,765
  • 105
  • 273
  • 257
Matthew
  • 7,605
  • 7
  • 39
  • 39

5 Answers5

517

Try 

CREATE TABLE foo LIKE bar;

so the keys and indexes are copied over as, well.

Documentation

RCNeil
  • 8,581
  • 12
  • 43
  • 61
  • 49
    This is a better answer since this copies indexes as well! – Chaitan Sep 28 '13 at 19:51
  • 3
    It is much better, but still doesn't copy foreign keys. – Josef Sábl Apr 17 '14 at 11:53
  • @RCNeil, Does `CREATE TABLE new_tbl LIKE orig_tbl;` copies privileges as well? Or must those be copied manually? – Pacerier Feb 09 '15 at 02:45
  • 2
    Why has this not been marked as the correct answer? – Programster Apr 04 '16 at 11:52
  • 4
    Probably because this answer came 4years later than the one marked as correct – pythonian29033 May 24 '16 at 09:05
  • RCNeil, as a non concerned : additions to your answer were rather useful. A good practice is to put the documentation, rather than just a link. So I don't clearly see why you was seemingly upset and reverted... – Pierre-Olivier Vares Feb 07 '17 at 09:41
  • 1
    @Pierre-OlivierVares The documentation text is not directly relevant to the actual answer. The answer text is concise in showing how to do what the question asked, while the documentation link merely provides additional context. As such, bloating the answer with text from the documentation is not helpful. – Abion47 Aug 13 '18 at 18:44
  • thank you! just used your solution to copy a table structure and it worked just fine :D – Mr.Flocker Aug 05 '22 at 12:22
167

Try:

CREATE TABLE foo SELECT * FROM bar LIMIT 0

Or:

CREATE TABLE foo SELECT * FROM bar WHERE 1=0
Andomar
  • 232,371
  • 49
  • 380
  • 404
  • how to do the same in sqlserver 2005? – Thunder Jan 21 '11 at 04:45
  • 4
    @Thunder: Try `select * into foo from bar where 1=0` – Andomar Jan 21 '11 at 12:32
  • If table bar has triggers associated with it, do you know if those come along as well? – Matt Sep 05 '12 at 16:15
  • 31
    This does not seem to copy the constraints or keys – Timo Huovinen Oct 27 '13 at 08:41
  • 16
    @TimoHuovinen: Perhaps [`create table NewTable like OldTable`](http://blog.sarathonline.com/2011/06/mysql-copying-table-structures.html) is an option for you. Link copied from deleted answer. – Andomar Oct 27 '13 at 09:13
  • 3
    Be *really* careful about using this under load and at high concurrency. I've just been burned using this at scale, where we created about 50 different tables based off the same source table, at the same time as inserting into the source table from other processes. Basically caused a massive lock meltdown and had to restart MySQL. – Mark B Jul 29 '14 at 15:57
  • 6
    Uhm, OP is confused? This is blatantly a wrong answer to the question, if we pay attention to the title and the emphasis in the question "... **without** copying over the data..." RCNell's answer is rightfully highest voted. Not sure why this one got accepted. Just sayin'. – xyphenor Jan 21 '16 at 03:58
  • It's ONLY for copying Column Names and Column Types. Without any additional data. – user3383675 Feb 26 '16 at 12:00
  • Why not simply `CREATE TABLE foo SELECT * FROM bar WHERE FALSE` ? – EvgenyKolyakov May 24 '17 at 12:51
  • Isn't advisable to avoid using the CREATE TABLE AS SELECT statement? https://www.percona.com/blog/2018/01/10/why-avoid-create-table-as-select-statement/ https://dev.mysql.com/doc/refman/5.7/en/replication-gtids-restrictions.html – Norah Borus Jul 24 '18 at 08:35
34
SHOW CREATE TABLE bar;

you will get a create statement for that table, edit the table name, or anything else you like, and then execute it.

This will allow you to copy the indexes and also manually tweak the table creation.

You can also run the query within a program.

Timo Huovinen
  • 53,325
  • 33
  • 152
  • 143
  • How could you convert `show create table bar` into a dynamically executable statement? – Pacerier Feb 02 '15 at 12:23
  • the result given by `show create table bar` is already executable, if the script has the permission to create tables, you could parse it and then execute alter table statements too. – Timo Huovinen Feb 05 '15 at 20:38
  • `create table...` will not copy indexes. this answer is the better option – bluepinto Sep 27 '19 at 13:56
2

Only want to clone the structure of table:

CREATE TABLE foo SELECT * FROM bar WHERE 1 = 2;

Also wants to copy the data:

CREATE TABLE foo as SELECT * FROM bar;
Ali Azaz Alam
  • 1,782
  • 1
  • 16
  • 27
0

I´d extend the answer to

CREATE TABLE DB.TABLE_copy LIKE DB.TABLE;

depending on the case, you could be more explicit to avoid any mistake. Thanks RCNeil.

Tatu IoT
  • 11
  • 2