CODEWITHSUNDEEP
mysqlsqldatabase
Shoaib Maroof
Shoaib Maroof

Reputation: 369

Looking for the existence of a column in a database (/table)

I do not have access to SQL Search.

I would like to see if the column 'system origin' exists within a database.

There are 4 possible field values for this column: W, X , Y or Z.

I do not have the exact column name or table that it may exist in. What would be the best way to see if it does or doesn't exist?

Upvotes: 1

Views: 54

Answers (1)

MyStream
MyStream

Reputation: 2553

SELECT c.table_name, c.column_name
  FROM INFORMATION_SCHEMA.COLUMNS c
ORDER BY c.table_name ASC, c.column_name ASC

A Query like this will help you list all the tables alphabetically, and all the columns in those tables alphabetically.

It may be faster than trying to search if you scan simply scan down the list?

EXTRA NOTE:

You could also add a WHERE, perhaps like this:

SELECT c.table_name, c.column_name
  FROM INFORMATION_SCHEMA.COLUMNS c
  WHERE c.column_name IN ('W','X','Y','Z')
ORDER BY c.table_name ASC, c.column_name ASC

Upvotes: 2

Related Questions