8

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?

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
Greg
  • 23,155
  • 11
  • 57
  • 79

2 Answers2

7

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.

Andomar
  • 232,371
  • 49
  • 380
  • 404
  • Sounds agreeable. I tried something similar which also failed. I just want to be sure that there isn't an edge case I'm missing. – Greg Oct 15 '10 at 21:18
1

If you were to look at the definition for the INFORMATION_SCHEMA.COLUMNS view, you'd see that it starts with the sys.objects table, looking for types of 'U' (Table, user-defined) or 'V' (view), so it is already performing the check for table existence for you.

CREATE VIEW INFORMATION_SCHEMA.COLUMNS  
AS  
SELECT
...
FROM  
 sys.objects o JOIN sys.columns c ON c.object_id = o.object_id  
 LEFT JOIN sys.types t ON c.user_type_id = t.user_type_id  
WHERE  
 o.type IN ('U', 'V')
Joe Stefanelli
  • 132,803
  • 19
  • 237
  • 235
  • You are confirming what I already state in my question. If a table can have zero columns, then this view alone won't help me distinguish between a table not existing and a table with no columns. My question is: can a table have zero columns? – Greg Oct 15 '10 at 23:16