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?
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With