Запрос для запросов

Формирует пакет SQL-запросов типа "SELECT" с фильтром по одному констрейнту (PK_PURPOSEFULGRANT) и с условием по другому констрейнту (PK_BUDGET):

Варианты попроще
(по одному констрейнту):

Для Oracle:

select 'select * from (' as STMT1 from dual union all
select
'select '''||c.table_name||'.'||cc.column_name||''' as TBLCOL, count(*) as CNT from '
||c.table_name
||' t where t.'||cc.column_name||' in (123456789) union all'
from user_constraints c
join user_cons_columns cc on cc.constraint_name = c.constraint_name
where c.r_constraint_name='PK_ESTIMATE'
union all select ') where CNT <> 0' from dual
;

Для Firebird:
/* в закомментированном блоке - отсылки к знакомым мне обозначениям из Oracle */

select 'select * from (' as STMT1 from rdb$database union all
select
/*
cc.rdb$index_name as CONSTRAINT_NAME
,trim(c.rdb$relation_name) as TABLE_NAME
,trim(cc.rdb$field_name) as COLUMN_NAME
,trim(c2c.rdb$field_name) as COLUMN_NAME2
,c.rdb$foreign_key as R_CONSTRAINT_NAME
,trim(cci.rdb$relation_name) as R_TABLE_NAME
,trim(ccs.rdb$field_name) as R_COLUMN_NAME
,trim(c.rdb$relation_name)||'.'||trim(cc.rdb$field_name) as TBL_COL
*/
'select '''||trim(c.rdb$relation_name)||'.'||trim(cc.rdb$field_name)||''' as TBL_COL, count(*) as CNT from '
||trim(c.rdb$relation_name)
||' t where t.'||trim(cc.rdb$field_name)||' in (123456789) union all' as SQLA
from rdb$indices c
join rdb$index_segments cc on cc.rdb$index_name = c.rdb$index_name
join rdb$index_segments ccs on ccs.rdb$index_name = c.rdb$foreign_key
join rdb$indices cci on cci.rdb$index_name = ccs.rdb$index_name
where
trim(c.rdb$foreign_key) = 'PK_ESTIMATE'
union all select ') where CNT<>0' from rdb$database
;

Вариант посложнее:
(по двум констрейнтам одновременно)

Для Oracle:

select 'select * from (' as STMT1 from dual union all
select
'select '''||c.table_name||'.'||cc.column_name||' ('||c2c.column_name||')'' as TBLCOL, count(*) as CNT from '
||c.table_name
||' t where t.'||cc.column_name||' is not null and exists (select null from PURPOSEFULGRANT p where p.ID=t.'||cc.column_name||' and p.BUDGET_ID<>t.'||c2c.column_name||') union all'
from user_constraints c
join user_cons_columns cc on cc.constraint_name = c.constraint_name
join user_constraints c2 on c2.table_name=c.table_name and c2.r_constraint_name='PK_BUDGET'
join user_cons_columns c2c on c2c.constraint_name = c2.constraint_name
where c.r_constraint_name='PK_PURPOSEFULGRANT'
union all select ') where CNT <> 0' from dual
;

Для Firebird:
/* в закомментированном блоке - отсылки к знакомым мне обозначениям из Oracle */

select 'select * from (' as STMT1 from rdb$database union all
select
/*
cc.rdb$index_name as CONSTRAINT_NAME
,trim(c.rdb$relation_name) as TABLE_NAME
,trim(cc.rdb$field_name) as COLUMN_NAME
,trim(c2c.rdb$field_name) as COLUMN_NAME2
,c.rdb$foreign_key as R_CONSTRAINT_NAME
,trim(cci.rdb$relation_name) as R_TABLE_NAME
,trim(ccs.rdb$field_name) as R_COLUMN_NAME
,trim(c.rdb$relation_name)||'.'||trim(cc.rdb$field_name) as TBL_COL
*/
'select '''||trim(c.rdb$relation_name)||'.'||trim(cc.rdb$field_name)||' ('||trim(c2c.rdb$field_name)||')'' as TBL_COL, count(*) as CNT from '
||trim(c.rdb$relation_name)
||' t where t.'||trim(cc.rdb$field_name)||' is not null and exists (select null from PURPOSEFULGRANT p where p.ID=t.'||trim(cc.rdb$field_name)||' and p.BUDGET_ID<>t.'||trim(c2c.rdb$field_name)||') union all' as SQLA
from rdb$indices c
join rdb$index_segments cc on cc.rdb$index_name = c.rdb$index_name
join rdb$index_segments ccs on ccs.rdb$index_name = c.rdb$foreign_key
join rdb$indices cci on cci.rdb$index_name = ccs.rdb$index_name
join rdb$indices c2 on c2.rdb$relation_name = c.rdb$relation_name and c2.rdb$foreign_key = 'PK_BUDGET'
join rdb$index_segments c2c on c2c.rdb$index_name = c2.rdb$index_name
where
trim(c.rdb$foreign_key) = 'PK_PURPOSEFULGRANT'
union all select ') where CNT<>0' from rdb$database
;