sysreferences is probably what you are looking for.

-- Shows the PK of the given table

select tabname[1,30] table, c.constrname[1,30] constraint_name
from systables t, sysconstraints c
where t.tabname = "<tablename>"
and c.constrtype = "P"
and c.tabid = t.tabid;

-- Shows the referencing tables & FK constraints
select t2.tabname[1,30] referencing_table, c.constrname[1,30] constraint_name
from systables t, sysreferences r, sysconstraints c, systables t2
where r.ptabid = t.tabid
and t.tabname = "<tablename>"
and r.constrid = c.constrid
and c.tabid = t2.tabid;

Mike,

I decided to add the t.tabname to the output so that I can see the name of the referenced table in information. That is a GREAT start. Now let's include the name(s) of the column(s) involved in the in the FK constraint. I took a guess at syscoldepend but that looked like a dead end.  I suspect this would be built like sysindexes, in violation of normalization rule[1]: No repeating groups. But it would make things easier. The idea is that once I've picked a row to purge, first open a prepared cursor to chase down corresponding rows in the detail table.  And recursively, because I see as many as 4 layers of dependency in the   one of the tables I'm going after.

If it gets too complicated I may have to give up in the face of diminishing returns.

No, I do not wish the change the constraint to "on delete cascade"; that affects the whole table. (YIKES!)

So let's continue along this path and see if it gets too hairy.

Thanks again for that GREAT solution.  I have, of course, saved it.  I will want to wrap in both Perl and shell scripts to parametrize it.

-- Jacob S

Mike,

I decided to add the t.tabname to the output so that I can see the name of the referenced table in information. That is a GREAT start. Now let's include the name(s) of the column(s) involved in the in the FK constraint. I took a guess at syscoldepend but that looked like a dead end.  I suspect this would be built like sysindexes, in violation of normalization rule[1]: No repeating groups. But it would make things easier. The idea is that once I've picked a row to purge, first open a prepared cursor to chase down corresponding rows in the detail table.  And recursively, because I see as many as 4 layers of dependency in the   one of the tables I'm going after.

If it gets too complicated I may have to give up in the face of diminishing returns.

No, I do not wish the change the constraint to "on delete cascade"; that affects the whole table. (YIKES!)

So let's continue along this path and see if it gets too hairy.

Thanks again for that GREAT solution.  I have, of course, saved it.  I will want to wrap in both Perl and shell scripts to parametrize it.

-- Jacob S

sysreferences is probably what you are looking for.

-- Shows the PK of the given table

select tabname[1,30] table, c.constrname[1,30] constraint_name
from systables t, sysconstraints c
where t.tabname = "<tablename>"
and c.constrtype = "P"
and c.tabid = t.tabid;

-- Shows the referencing tables & FK constraints
select t2.tabname[1,30] referencing_table, c.constrname[1,30] constraint_name
from systables t, sysreferences r, sysconstraints c, systables t2
where r.ptabid = t.tabid
and t.tabname = "<tablename>"
and r.constrid = c.constrid
and c.tabid = t2.tabid;

Jacob:

Here's mine. This is how myschema does it with the -F option:

Mike,

I decided to add the t.tabname to the output so that I can see the name of the referenced table in information. That is a GREAT start. Now let's include the name(s) of the column(s) involved in the in the FK constraint. I took a guess at syscoldepend but that looked like a dead end.  I suspect this would be built like sysindexes, in violation of normalization rule[1]: No repeating groups. But it would make things easier. The idea is that once I've picked a row to purge, first open a prepared cursor to chase down corresponding rows in the detail table.  And recursively, because I see as many as 4 layers of dependency in the   one of the tables I'm going after.

If it gets too complicated I may have to give up in the face of diminishing returns.

No, I do not wish the change the constraint to "on delete cascade"; that affects the whole table. (YIKES!)

So let's continue along this path and see if it gets too hairy.

Thanks again for that GREAT solution.  I have, of course, saved it.  I will want to wrap in both Perl and shell scripts to parametrize it.

-- Jacob S

sysreferences is probably what you are looking for.

-- Shows the PK of the given table

select tabname[1,30] table, c.constrname[1,30] constraint_name
from systables t, sysconstraints c
where t.tabname = "<tablename>"
and c.constrtype = "P"
and c.tabid = t.tabid;

-- Shows the referencing tables & FK constraints
select t2.tabname[1,30] referencing_table, c.constrname[1,30] constraint_name
from systables t, sysreferences r, sysconstraints c, systables t2
where r.ptabid = t.tabid
and t.tabname = "<tablename>"
and r.constrid = c.constrid
and c.tabid = t2.tabid;

Thanks MUCH Doug and Art.

Since this will be going into a Perl utility, a cut-down version of Art's version, however intimidating, might be more suited to my purpose.

However, Art, without comments I am a bit confused.  I see that both st and rt are aliases for systables. One is being use for the referenced table and  the other for the referncee table.  Based on my knowledge that the table I'm working with has 7 referencing tables, I see that rt.tabname is the name of the referenced table..

In order to run this in dbaccess I would need to provide valued for the "?" placeholders.  For the second ? placeholder I substituted my table. But clause "AND st.tabname MATCHES ?" i had to comment out; I could not think of a matching pattern, nor do I see a need for it in my situation.

And, after cutting down the number of columns retrieved, I see the column numbers but not hte more-useful (for me) column names.   I will play with that a bit and post simpler (again, for my purposes) version, but with column names.  As it happens, I don't believe ANY of out PK/FK constraints in my servers are more than 3 columns so I commented those out of consideration.

This is GREAT, Art.

Jacob:

Here's mine. This is how myschema does it with the -F option:

Mike,

I decided to add the t.tabname to the output so that I can see the name of the referenced table in information. That is a GREAT start. Now let's include the name(s) of the column(s) involved in the in the FK constraint. I took a guess at syscoldepend but that looked like a dead end.  I suspect this would be built like sysindexes, in violation of normalization rule[1]: No repeating groups. But it would make things easier. The idea is that once I've picked a row to purge, first open a prepared cursor to chase down corresponding rows in the detail table.  And recursively, because I see as many as 4 layers of dependency in the   one of the tables I'm going after.

If it gets too complicated I may have to give up in the face of diminishing returns.

No, I do not wish the change the constraint to "on delete cascade"; that affects the whole table. (YIKES!)

So let's continue along this path and see if it gets too hairy.

Thanks again for that GREAT solution.  I have, of course, saved it.  I will want to wrap in both Perl and shell scripts to parametrize it.

-- Jacob S

sysreferences is probably what you are looking for.

-- Shows the PK of the given table

select tabname[1,30] table, c.constrname[1,30] constraint_name
from systables t, sysconstraints c
where t.tabname = "<tablename>"
and c.constrtype = "P"
and c.tabid = t.tabid;

-- Shows the referencing tables & FK constraints
select t2.tabname[1,30] referencing_table, c.constrname[1,30] constraint_name
from systables t, sysreferences r, sysconstraints c, systables t2
where r.ptabid = t.tabid
and t.tabname = "<tablename>"
and r.constrid = c.constrid
and c.tabid = t2.tabid;

Jacob:

So, to get the column names, you have to map the colno's retrieved from the "partN"'s to the table's syscolumns records. I don't do that in the same query in myschema mainly because that would take an already complex 8 table join up to 24 tables (having to join syscolumns up to 16 times as outer joins). Myschema looks up the non-zero "partN" numbers individually. Note that if a particular "partN" in the target index is negative then it is a DECending key element and the actual colno is -1 * partN. Fortunately you do not have to worry about any functional key elements in a foreign key constraint index or its referenced primary or unique key constraint index.

Art

Thanks MUCH Doug and Art.

Since this will be going into a Perl utility, a cut-down version of Art's version, however intimidating, might be more suited to my purpose.

However, Art, without comments I am a bit confused.  I see that both st and rt are aliases for systables. One is being use for the referenced table and  the other for the referncee table.  Based on my knowledge that the table I'm working with has 7 referencing tables, I see that rt.tabname is the name of the referenced table..

In order to run this in dbaccess I would need to provide valued for the "?" placeholders.  For the second ? placeholder I substituted my table. But clause "AND st.tabname MATCHES ?" i had to comment out; I could not think of a matching pattern, nor do I see a need for it in my situation.

And, after cutting down the number of columns retrieved, I see the column numbers but not hte more-useful (for me) column names.   I will play with that a bit and post simpler (again, for my purposes) version, but with column names.  As it happens, I don't believe ANY of out PK/FK constraints in my servers are more than 3 columns so I commented those out of consideration.

This is GREAT, Art.

Jacob:

Here's mine. This is how myschema does it with the -F option:

Mike,

I decided to add the t.tabname to the output so that I can see the name of the referenced table in information. That is a GREAT start. Now let's include the name(s) of the column(s) involved in the in the FK constraint. I took a guess at syscoldepend but that looked like a dead end.  I suspect this would be built like sysindexes, in violation of normalization rule[1]: No repeating groups. But it would make things easier. The idea is that once I've picked a row to purge, first open a prepared cursor to chase down corresponding rows in the detail table.  And recursively, because I see as many as 4 layers of dependency in the   one of the tables I'm going after.

If it gets too complicated I may have to give up in the face of diminishing returns.

No, I do not wish the change the constraint to "on delete cascade"; that affects the whole table. (YIKES!)

So let's continue along this path and see if it gets too hairy.

Thanks again for that GREAT solution.  I have, of course, saved it.  I will want to wrap in both Perl and shell scripts to parametrize it.

-- Jacob S

sysreferences is probably what you are looking for.

-- Shows the PK of the given table

select tabname[1,30] table, c.constrname[1,30] constraint_name
from systables t, sysconstraints c
where t.tabname = "<tablename>"
and c.constrtype = "P"
and c.tabid = t.tabid;

-- Shows the referencing tables & FK constraints
select t2.tabname[1,30] referencing_table, c.constrname[1,30] constraint_name
from systables t, sysreferences r, sysconstraints c, systables t2
where r.ptabid = t.tabid
and t.tabname = "<tablename>"
and r.constrid = c.constrid
and c.tabid = t2.tabid;