CODEWITHSUNDEEP
mysqlmysql-workbench
Mārcis Liepiņš
Mārcis Liepiņš

Reputation: 76

Show column name MySQL

Is there a way to get a column name from table where all values in this column are the same!

Example! IF i would there would be a such a code it would return answer 'Works'

Table1
ID      Name     Works
1       Andre     Yes
2       John      Yes
3       Stewart   Yes

Upvotes: 2

Views: 503

Answers (2)

Norris
Norris

Reputation: 49

I don't know if the columns of the table are known. If not, you could be able to get them by:

desc Table1;

or if you are using higher version of MySQL, you could use:

select column_name from information_schema.columns where table_schema='your_schema' and table_name='Table1';

Then you try the following statement with parameters @column being replaced with the column names retrieved from the above statement:

select count(*) from (select count(*) as c from Table1 as t group by t.@column) as sub;

If the result is 1, the column is what you want. The result means how many distinct values this column has.

I suppose you will have to use a kind of programming language or stored procedure. You are not likely being able to achieve that with one single SQL statement.

Upvotes: 1

xims
xims

Reputation: 1608

I'm not sure if it's possible to run from MySQL itself but you can do this from your scripting engine.

You can run a loop on each column and do a query:

SELECT 
  COUNT(DISTINCT column_name) 
  FROM table_name;

And see you get 1 record in the results

Upvotes: 0

Related Questions