Oracle metadata

1: finding tables
SELECT
TABLE_NAME
FROM
ALL_TABLES
WHERE
TABLE_NAME LIKE '%PATTERN%'
ORDER
BY TABLE_NAME;

2: finding columns
SELECT
TABLE_NAME,
COLUMN_NAME
FROM
ALL_TAB_COLUMNS
WHERE
COLUMN_NAME LIKE '%PATTERN%';

3: counting rows of columns
COLUMN DUMMY NOPRINT
COMPUTE SUM OF NUM_ROWS ON DUMMY
BREAK ON DUMMY
SELECT
NULL DUMMY,
T.TABLE_NAME,
C.COLUMN_NAME,
T.NUM_ROWS
FROM
ALL_TABLES T,
ALL_TAB_COLUMNS C
WHERE
T.TABLE_NAME = C.TABLE_NAME
AND C.COLUMN_NAME LIKE '%PATTERN%'
ORDER BY T.TABLE_NAME;