0

I have a dropdownlist which is being populated by column names based on the table name selected by user in the previous dropdown. I am using the following query

SHOW columns from abcTableName LIKE '%name' which works

I want all to include all the columns names except a few columns. Therefore , I want the query like this 

SHOW columns from abcTable NOT LIKE ('%name','%pk','%fk')

which does not work . Even 

SHOW columns from abcTable NOT LIKE '%name'

does not work

Currently I run two loops to fetch the columns names- outer loop to pass the table name and inner loop to pass the parameters to the query which takes a lot of time .I want to optimize it.

Can anyone please suggest ?

aynber
  • 22,380
  • 8
  • 50
  • 63
Jagruti
  • 312
  • 4
  • 18

2 Answers2

1

You could use a more formal method:

SELECT COLUMN_NAME
FROM information_schema.columns
WHERE 
    table_schema = '[database]' AND
    table_name = '[table_name]' AND
    COLUMN_NAME LIKE '%name' AND
    COLUMN_NAME NOT LIKE '%pk' AND
    COLUMN_NAME NOT LIKE '%fk';
Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
  • The first solution says `You have an error in your SQL syntax; check the manual that corresponds to your MySQL ` . The second one gives the following message `Operand should contain 1 column(s)` – Jagruti May 07 '18 at 10:56
  • @ALearner My code is untested, but you see above now should run for you. – Tim Biegeleisen May 07 '18 at 10:58
  • Thanks .This would help too. I wish I could accept both the solutions as answer – Jagruti May 07 '18 at 11:01
0

use where clause 

SHOW columns from abcTable where field not like '%name'

look at Extensions to SHOW Statements https://dev.mysql.com/doc/refman/8.0/en/extended-show.html

Emad Elpurgy
  • 347
  • 1
  • 3
  • 8