Oracle 18c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE tab(id_Support INT, col VARCHAR2(20));
CREATE TABLE tab2(id_Support INT, col VARCHAR2(20));
INSERT INTO tab2(id_support, col) VALUES (1, 'some text');
1 rows affected

CREATE TABLE tab3(id_Support INT, col VARCHAR2(20));
INSERT INTO tab3(id_support) VALUES (1);
1 rows affected

INSERT INTO tab3(id_support) VALUES (10);
1 rows affected

INSERT INTO tab3(id_support) VALUES (20);
1 rows affected

 SELECT owner, table_name, cnt              
    FROM all_tab_columns, XMLTABLE('/ROWSET/ROW' passing 
                 (dbms_xmlgen.getxmltype(REPLACE(REPLACE(
                 'select COUNT(*) AS cnt from <owner>.<table_name>', '<owner>', owner)
                 , '<table_name>', table_name))) COLUMNS cnt INT)
    WHERE column_name LIKE '%ID_SUPPORT%'
      AND cnt > 0;


OWNER
TABLE_NAME
CNT


FIDDLE_BBRAZRAJHABBHYJNTWLM
TAB3
3

FIDDLE_BBRAZRAJHABBHYJNTWLM
TAB2
1


-- adding example row column
 SELECT owner, table_name, cnt, example             
    FROM all_tab_columns, XMLTABLE('/ROWSET/ROW' passing 
                 (dbms_xmlgen.getxmltype(REPLACE(REPLACE(
 'select COUNT(*) AS cnt, MAX((SELECT JSON_ARRAYAGG(JSON_OBJECT(''id_support'' VALUE id_Support, ''col'' VALUE col)) FROM <owner>.<table_name> WHERE rownum<10)) as example from <owner>.<table_name>', '<owner>', owner)
                 , '<table_name>', table_name))) 
                 COLUMNS cnt INT
                        , example VARCHAR2(1000))
    WHERE column_name LIKE '%ID_SUPPORT%'
      AND cnt > 0;


OWNER
TABLE_NAME
CNT
EXAMPLE


FIDDLE_BBRAZRAJHABBHYJNTWLM
TAB3
3
[{"id_support":1,"col":null},{"id_support":10,"col":null},{"id_support":20,"col":null}]

FIDDLE_BBRAZRAJHABBHYJNTWLM
TAB2
1
[{"id_support":1,"col":"some text"}]


-- generic approach Oracle 19c
 SELECT owner, table_name, cnt, example             
    FROM all_tab_columns, XMLTABLE('/ROWSET/ROW' passing 
                 (dbms_xmlgen.getxmltype(REPLACE(REPLACE(
 'select COUNT(*) AS cnt, MAX((SELECT JSON_ARRAYAGG(JSON_OBJECT(*)) FROM <owner>.<table_name> WHERE rownum<10)) as example from <owner>.<table_name>', '<owner>', owner)
                 , '<table_name>', table_name))) 
                 COLUMNS cnt INT
                        , example VARCHAR2(1000))
    WHERE column_name LIKE '%ID_SUPPORT%'
      AND cnt > 0;
ORA-19202: Error occurred in XML processing
ORA-00936: missing expression
ORA-06512: at "SYS.DBMS_XMLGEN", line 288
ORA-06512: at "SYS.DBMS_XMLGEN", line 237
ORA-06512: at "SYS.DBMS_XMLGEN", line 280
ORA-06512: at line 1

OWNER	TABLE_NAME	CNT
FIDDLE_BBRAZRAJHABBHYJNTWLM	TAB3	3
FIDDLE_BBRAZRAJHABBHYJNTWLM	TAB2	1

OWNER	TABLE_NAME	CNT	EXAMPLE
FIDDLE_BBRAZRAJHABBHYJNTWLM	TAB3	3	[{"id_support":1,"col":null},{"id_support":10,"col":null},{"id_support":20,"col":null}]
FIDDLE_BBRAZRAJHABBHYJNTWLM	TAB2	1	[{"id_support":1,"col":"some text"}]