27

Can I create a table without any columns in SQL Server by t-sql?

Jon Seigel
  • 12,251
  • 8
  • 58
  • 92
loviji
  • 12,620
  • 17
  • 63
  • 94
  • 2
    Why do you need it? You can create select statements without from clause. – Andrew Bezzub Mar 13 '10 at 12:37
  • 5
    @Andrew Bezzub: because, i want firstly create a table, then add columns to table. i know, it is not possible select without from clause. for solution my task i can add some one column, then remove it. – loviji Mar 13 '10 at 12:40
  • 1
    Instead of creating an empty place-holder table, can you wait to create the table once you know what the first column is? – David R Tribble Mar 16 '10 at 03:56
  • [It would be possible in PostgreSQL](https://blog.jooq.org/2017/03/17/creating-tables-dum-and-dee-in-postgresql/) – Lukas Eder Mar 17 '17 at 14:22

2 Answers2

23

A table is a collection of columns and rows. You need at least one column.

Digital gig
  • 254
  • 1
  • 2
  • 7
    More precisely: “A table is a collection of at least one column and at least zero rows” – Joey Mar 13 '10 at 12:53
  • 6
    in postgresql you *can* indeed create tables without columns. But don't ask me why. – codymanix Apr 06 '10 at 13:06
  • 1
    This seems to be supported by this SQL Server 2005 error message: CREATE TABLE NoColumns (doomed INT) ALTER TABLE NoColumns DROP COLUMN doomed -- "ALTER TABLE DROP COLUMN failed because 'doomed' is the only data column in table 'NoColumns'. A table must have at least one data column." Sad but (apparently) true . . . – Woody Zenfell III Jun 03 '10 at 19:37
  • 1
    @codymanix: Columnless tables are used for [partitioning](http://www.postgresql.org/docs/9.3/static/ddl-partitioning.html). – Snowball Dec 02 '14 at 17:22
  • @codymanix Also there use case, when you have table with dynamic column and it is possible when you have no columns in table. This what I have encountered xD – Nikita Jul 02 '19 at 15:30
-3

No. What would you use it for?

Ray
  • 21,485
  • 5
  • 48
  • 64
  • 26
    For example, let's say you have code that creates any missing columns in a table and you also want to handle the case where the table itself is missing. It would be cleaner to be able to create the table if necessary and then call the code to create any missing columns. – mhenry1384 May 14 '12 at 02:06
  • Lets say for example you want to copy all the columns and rows from one table to a new table without having to recreate the columns and row data of the old table – Emmanuel Njorodongo Mar 15 '21 at 17:04