0

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,438
  • 68
  • 55
  • 126
Emad-ud-deen
  • 4,532
  • 19
  • 80
  • 148

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
  • 10,740
  • 5
  • 42
  • 61
  • 1
    Thank you very much for the statement. – Emad-ud-deen Nov 27 '11 at 16:27
  • 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