identifying columns in all the tables with a specific value

>>where the value is -1
I didn't understand this bit, how will a count of rows result in -1 ???
is the requirement fully stated?

sorry i think i was not clear

select count(*) from abc_fct where reg_id = -1
select count(*) from abc_fct where usr_id = -1

this way I want to dynamically find the counts for all the tables ending with %FCT for the 4 columns (reg_id, usr_id,cat_id, spr_id

Is there a way to do this with dynamic sql..

thanks

with the suggested inner join...

BEGIN
    FOR i IN (
                SELECT
                TABLE_NAME
                FROM (
                      SELECT
                      DISTINCT utc.TABLE_NAME
                      , utc.COLUMN_NAME
                      , row_number() over (partition BY utc.TABLE_NAME ORDER BY utc.COLUMN_NAME) num_of
                      FROM user_tab_columns utc
                      INNER JOIN user_tables ut ON utc.TABLE_NAME = ut.TABLE_NAME
                      WHERE COLUMN_NAME = 'REG_ID'
                      OR COLUMN_NAME = 'USR_ID'
                      OR COLUMN_NAME = 'CAT_ID'
                      OR COLUMN_NAME = 'SPR_ID'
                     )
                WHERE num_of = 4
              )
    LOOP
        EXECUTE IMMEDIATE 'select count(*) from ' || i.TABLE_NAME || ' where reg_id = -1';
    END LOOP;
END;

Select allOpen in new window

humble apologies, 3rd and final attempt. This should output the table_name, which you probably need:

BEGIN
    FOR i IN (
                SELECT
                TABLE_NAME
                FROM (
                      SELECT
                      DISTINCT utc.TABLE_NAME
                      , utc.COLUMN_NAME
                      , row_number() over (partition BY utc.TABLE_NAME ORDER BY utc.COLUMN_NAME) num_of
                      FROM user_tab_columns utc
                      INNER JOIN user_table ut ON utc.TABLE_NAME = ut.TABLE_NAME
                      WHERE utc.COLUMN_NAME = 'REG_ID'
                      OR utc.COLUMN_NAME = 'USR_ID'
                      OR utc.COLUMN_NAME = 'CAT_ID'
                      OR utc.COLUMN_NAME = 'SPR_ID'
                     )
                WHERE num_of = 4
              )
    LOOP
        EXECUTE IMMEDIATE 'select ''' || i.TABLE_NAME ||''' as table_name, count(*) as count_of from ' || i.TABLE_NAME || ' where reg_id = -1';
    END LOOP;
END;

Select allOpen in new window

ah, I see I have it wrong, the count is per field - duh! Oh well.

hats off to you sdstuber!!

I offer up a variant inspired by xmlgen:

SELECT
  TABLE_NAME
, TO_NUMBER(
    EXTRACTVALUE(
      DBMS_XMLGEN.getxmltype(
        'select count(distinct REG_ID || USR_ID || CAT_ID || SPR_ID) X from ' || table_name || ' where REG_ID = -1'
      ),
      '/ROWSET/ROW/X'
    )
  )
  as DISTINCT_COUNT_OF
FROM (
  SELECT
  DISTINCT utc.TABLE_NAME
  , utc.COLUMN_NAME
  , row_number() over (partition BY utc.TABLE_NAME ORDER BY utc.COLUMN_NAME) num_of
  FROM user_tab_columns utc
  INNER JOIN user_tables ut ON utc.TABLE_NAME = ut.TABLE_NAME
  WHERE utc.COLUMN_NAME = 'REG_ID'
  OR utc.COLUMN_NAME = 'USR_ID'
  OR utc.COLUMN_NAME = 'CAT_ID'
  OR utc.COLUMN_NAME = 'SPR_ID'
)
WHERE num_of = 4

Select allOpen in new window

woo hooo, this xmlgen is too enticing - apologies, but one last one. For an output like:
TABLE_NAME DISTINCT4 REG_ID USR_ID CAT_ID SPR_ID

SELECT
  TABLE_NAME
, TO_NUMBER(
    EXTRACTVALUE(
      DBMS_XMLGEN.getxmltype(
        'select count(distinct REG_ID || USR_ID || CAT_ID || SPR_ID) DISTINCT4 from ' || table_name 
      ),
      '/ROWSET/ROW/DISTINCT4'
    )
  )
  as DISTINCT_COUNT_OF
, TO_NUMBER(
     EXTRACTVALUE(
        DBMS_XMLGEN.getxmltype(
             'select count(*) REG_ID from ' || table_name || ' where REG_ID = -1'
         ),
         '/ROWSET/ROW/REG_ID'
     )
  )
  AS REG_ID
, TO_NUMBER(
     EXTRACTVALUE(
        DBMS_XMLGEN.getxmltype(
             'select count(*) USR_ID from ' || table_name || ' where USR_ID = -1'
         ),
         '/ROWSET/ROW/USR_ID'
     )
  )
  AS USR_ID
, TO_NUMBER(
     EXTRACTVALUE(
        DBMS_XMLGEN.getxmltype(
             'select count(*) CAT_ID from ' || table_name || ' where CAT_ID = -1'
         ),
         '/ROWSET/ROW/CAT_ID'
     )
  )
  AS CAT_ID
, TO_NUMBER(
     EXTRACTVALUE(
        DBMS_XMLGEN.getxmltype(
             'select count(*) SPR_ID from ' || table_name || ' where SPR_ID = -1'
         ),
         '/ROWSET/ROW/SPR_ID'
     )
  )
  AS SPR_ID
FROM (
      SELECT
      DISTINCT utc.TABLE_NAME
      , utc.COLUMN_NAME
      , row_number() over (partition BY utc.TABLE_NAME ORDER BY utc.COLUMN_NAME) num_of
      FROM user_tab_columns utc
      INNER JOIN user_tables ut ON utc.TABLE_NAME = ut.TABLE_NAME
      WHERE utc.COLUMN_NAME = 'REG_ID'
      OR utc.COLUMN_NAME = 'USR_ID'
      OR utc.COLUMN_NAME = 'CAT_ID'
      OR utc.COLUMN_NAME = 'SPR_ID'
)
WHERE num_of = 4

Select allOpen in new window

no points pl.

I tried all the solutions and all the solution works fine. Just curious which would be faster..calling function or using xmlgen type..

Please advice..

Thanks

I personally don't know. I have never used the DBMS_XMLGEN.getxmltype() function. I would try a sample if data and test both methods to see which is faster.

Without testing, I would guess the DBMS_XMLGEN.getxmltype() function because it is precompiled.

In my experience, some efficiency techniques work fine for most situations, but there is always a database design or query that breaks the normal rules of efficient coding.

execute immediate is a bit 'kludgy' really, you prepare a string that is to be a query, then execute each query - so that approach is performing a lot of individual queries.

My guess is the DBMS_XMLGEN will outshine the (more old fashioned) execute immediate.
I don't get many opportunities to use that function so I don't think of it as a first choice - but will attempt to do so in the future.

the DBMS_XMLGEN is still performing quite a number of individual queries then parsing each to access a specific node in the xml output, but those XML functions are quite fast.

Perhaps you could do explain plans as a way to assess the relative merits?

DBMS_XMLGEN is expensive,  for one, you do still need to parse and execute (effectively doing execute immediate anyway) plus the results are turned into xml, then the xml is parsed to pull out the values you really need.

However, a "run sql everywhere" type action is inherently an expensive operation so the added weight of dbms_xmlgen is probably negligible; then again, the added weight of looping in your own pl/sql is probably negligible as well.

I use dbms_xmlgen simply because a sql result set is generally easier to work with.
I do not use it for efficiency.

If you must get this information quickly, then time them, use autotrace or other session statistic measures and determine for yourself on your real data which is most efficient.

@ianmills

Is there a way to get the total count as well in same function call.. I tried passing the primary key column..and PK indicator as a parameter to function and modified the function as below to get a total couunt if the pased column is PK..But since many of the tables do not have a PK defined, for some of the tables the total count came blank..

CREATE OR REPLACE FUNCTION my_get_count (p_table VARCHAR2, p_column VARCHAR2, PK_IND)
    RETURN NUMBER IS
    query_str VARCHAR2(1000);
    num_count NUMBER;
BEGIN
if PK_IND = 'NP' then
    query_str := 'SELECT COUNT(*) FROM ' || p_table
        || ' WHERE ' || p_column || ' = -1';
elsif PK_IND = 'P'
   query_str := 'SELECT COUNT(*) FROM ' || p_table
 end if;
       
    EXECUTE IMMEDIATE query_str
        INTO num_count;
       
    RETURN num_count;
END;

I joined with all_constraints and all_constraint_cols to assign a PK_IND for a column..

Please let me know if you have any thoughts

Thanks

to get a total count, how about something like this...


select table_name, column_name, cnt,  sum(cnt) over() total_count from
(SELECT table_name,
       column_name,
       TO_NUMBER(
           EXTRACTVALUE(
               DBMS_XMLGEN.getxmltype(
                   'select count(*) X from ' || table_name || ' where ' || column_name || ' = -1'
               ),
               '/ROWSET/ROW/X'
           )
       )
           cnt
  FROM all_tab_cols
 WHERE owner = 'MY_SCHEMA'
   AND table_name LIKE '%FCT'
   AND column_name IN ('REG_ID', 'USR_ID', 'CAT_ID', 'SPR_ID')
ORDER BY table_name, column_name)

@sdstuber

Thank you for your response. I think i was not clear. I am not looking for total of the columns with -1. I want to get the total count of the whole table as shown below

Tab_name    Col_name    Cnt
ABC_FCT       total             5500
ABC_FCT       reg_id          10
ABC_FCT       usr_id           5
ABC_FCT       cat_id           6
ABC_FCT       spr_id           11
XYZ_FCT       total              6000
XYZ_FCT       reg_id          10
XYZ_FCT       usr_id           5
XYZ_FCT       cat_id           6
XYZ_FCT       spr_id          11

Though total is not the name of a column in the table, i want to display the 'total' under column name so that I can pivot this.

Hence I passed the PK of the table to the function so that I could get the total count. Though I am successful getting what I want in the above format, some of the tables do not have PK and hence total is blank..

On the other note I will run both methods, calling function and xmlgen to measure the performance..

thanks

>>some of the tables do not have PK and hence total is blank..
just count(*) if all you want is total rows, no need to specify a field.

I figured this out..Thanks for the great answers..