Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Search database if column name/field name exists in a table in mySQL

Is there a way to search the database if a column name / field name exists in a table in mysql?

like image 846
rjmcb Avatar asked Apr 11 '12 06:04

rjmcb


People also ask

How do you check whether a column is existing in a table in MySQL?

Find if the column exists using the SQL below: SELECT column_name FROM INFORMATION_SCHEMA . COLUMNS WHERE TABLE_SCHEMA =[Database Name] AND TABLE_NAME =[Table Name]; If the above query returns a result then it means the column exists, otherwise you can go ahead and create the column.

How do I find a specific column in a MySQL database?

How to list all tables that contain a specific column name in MySQL? You want to look for tables using the name of columns in them. SELECT DISTINCT TABLE_NAME FROM INFORMATION_SCHEMA. COLUMNS WHERE COLUMN_NAME IN('column1', 'column2') AND TABLE_SCHEMA = 'schema_name';

Can I search a SQL database for a column name?

A feature that can be used to search for column names in SQL Server is Object search. This feature allows users to find all SQL objects containing the specified phrase.


2 Answers

use INFORMATION_SCHEMA database and its tables.

eg :

    SELECT *
FROM   information_schema.columns
WHERE  table_schema = 'MY_DATABASE'
       AND column_name IN ( 'MY_COLUMN_NAME' );  
like image 168
DhruvPathak Avatar answered Sep 22 '22 02:09

DhruvPathak


If you want to search in the whole database then you should try

SELECT * 
FROM information_schema.COLUMNS 
WHERE 
    TABLE_SCHEMA = 'db_name' 
AND COLUMN_NAME = 'column_name'

And if you want to search in the particular table then you should try

SELECT * 
FROM information_schema.COLUMNS 
WHERE 
    TABLE_SCHEMA = 'db_name' 
AND TABLE_NAME = 'table_name' 
AND COLUMN_NAME = 'column_name'
like image 6
vikiiii Avatar answered Sep 22 '22 02:09

vikiiii