1

I would like to know what query to use to determine if a particular table column exists in a given table.

For example I would like to know if the column named Address_1 exists in the table named Visits.

I think it is in a data dictionary table maybe.

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
Emad-ud-deen
  • 4,734
  • 21
  • 87
  • 152

1 Answers1

-2

Edit: As pointed out by @tidwall, this answer is for SQL Server and won't work for sqlite. This stackoverflow thread has the right answer for sqlite.

SELECT name
FROM sysobjects
WHERE id IN (
    SELECT id
    FROM syscolumns
    WHERE name = 'THE_COLUMN_NAME'
)

also, this yields all the table column information for your parsing enjoyment:

SELECT COLUMN_NAME, data_type, character_maximum_length
FROM information_schema.columns
WHERE TABLE_NAME = 'your_table_name'
vzwick
  • 11,008
  • 5
  • 43
  • 63
  • I tried the first query but I get a "no such table exists" error so I will try the other statement using the Count(*) function which should be as good for me. – Emad-ud-deen Nov 27 '11 at 16:46
  • 1
    @vzwick: This question is for SQLite, not SQL Server. `syscolumns` does not exist in SQLite. The answer is [here](http://stackoverflow.com/questions/604939/how-can-i-get-the-list-of-a-columns-in-a-table-for-a-sqlite-database). – tidwall Nov 27 '11 at 21:14
  • @tidwall Thanks for the pointer, updated the answer accordingly. – vzwick Mar 09 '15 at 10:33