search for a column name inside the whole database

Question

how to search the whole database for a column name in sqli.

Suppose we have 160 tables with many columns i want to look for animals column inside the database. how would i do that. I have seen couple of examples here mostly they search the column name if the table name is known. but what if i dont know the table itself..??

table 1, table 2..... table 160 and i am looking for "animal" column in the whole database.

i tried if the table is known its working

SELECT * FROM TABLE_NAME WHERE COLUMN_NAME='animal'

Possible duplicate of http://stackoverflow.com/questions/5648420/get-all-columns-from-all-mysql-tables — NMK, Oct 14 '14 at 09:50

Ullas · Accepted Answer · 2014-10-14T10:01:29.810

1

Query

SELECT table_name
FROM INFORMATION_SCHEMA.COLUMNS
WHERE column_name='animal';

Read about INFORMATION_SCHEMA here

edited Oct 14 '14 at 10:01

answered Oct 14 '14 at 09:49

Ullas

11,450
4
33
50

score 0 · Answer 2 · answered Oct 14 '14 at 09:55

JDBC has a vendor independant solution: DatabaseMetaData.getColumns.

public static void processColumn(Connection con, String column) throws SQLException {
    DatabaseMetaData metadata = con.getMetaData();
    String catalog = null;
    String schema = null;
    String table = null;
    String columnPattern = column;
    try (ResultSet rs = metadata.getColumns(catalog, schema, table, columnPattern)) {
        System.out.printf("Table %s%n", rs.getString("TABLE_NAME"));
    }
}

null serves as wildcard.

search for a column name inside the whole database

2 Answers2

Query