Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Is it possible for an SQL table to have zero columns?

Tags:

sql

sql-server

I'm using the following query to gather information about a table's columns:

SELECT COLUMN_NAME,
       ORDINAL_POSITION,
       DATA_TYPE,
       CHARACTER_MAXIMUM_LENGTH,
       Is_NULLABLE
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_NAME = 'TableName'
    ORDER BY ORDINAL_POSITION

If this query returns zero results, can I safety declare that the table doesn't exist? Or is is somehow possible that the table exists but (perversely) has no columns?

I'm already querying INFORMATION_SCHEMA.TABLES to find out if the table exists, but I'd like to cut it down to one query if possible.

For future reference, I found these related questions:
Create a table without columns
Can I select 0 columns in SQL Server?

like image 710
Greg Avatar asked Oct 15 '10 21:10

Greg


1 Answers

If you try:

create table TestTable (id int)
alter table TestTable drop column id

SQL Server complains that:

Msg 4923, Level 16, State 1, Line 1
ALTER TABLE DROP COLUMN failed because 'id' is the only data column in table
'TestTable'. A table must have at least one data column.

So a table must always have at least one column.

like image 122
Andomar Avatar answered Nov 15 '22 19:11

Andomar