Search code examples
mysqlsql-likedatabase-schemashow-sql

use 'not like' in mySQL show statements


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 ?


Solution

  • 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';