what is related with?
Hi, I am trying to find one query to identify all table names with connections for my foreign keys... Thanks in advance for any help, SP ------------------------------ Sergio Peres AIRC Coimbra ------------------------------ #Informix
Hmm, several options based on what you really need to know:
If what tables are dependent on a given table, you can use myschema with the -F option:
myschema -d art -t 'parent' -F -q
CREATE TABLE "informix".parent (
one SERIAL(1) NOT NULL
) IN datadbs_1 EXTENT SIZE 16 NEXT SIZE 16 LOCK MODE ROW;
{
Please review extent sizing and adjust to allow for growth.
}
REVOKE ALL ON "informix".parent FROM public AS "informix";CREATE UNIQUE INDEX "informix".p659_7249 ON "informix".parent (
one ASC
) USING btree IN datadbs_1;
GRANT SELECT, UPDATE, INSERT, DELETE, INDEX ON "informix".parent TO "public" AS "informix";ALTER TABLE "informix".parent ADD CONSTRAINT PRIMARY KEY (
one
) ;
ALTER TABLE "art".child ADD CONSTRAINT FOREIGN KEY (
one
) REFERENCES parent (
one
) ;
ALTER TABLE "art".other_child ADD CONSTRAINT FOREIGN KEY (
one
) REFERENCES parent (
one
) CONSTRAINT "art".other_child_fk1;
If you need to know every reference, you can:
myschema -q -d art --constraint-file=fk /dev/null
The file (fk) will contain all of the primary keys followed by the unique keys followed by th foreign keys.
If you really need a query telling you what tables are referring to which tables, then:
SELECT st.tabname, st.owner, rt.tabname, rt.owner, sr.primary, sr.ptabid,
sr.delrule, sc.constrid, sc.constrname, sc.constrtype, sc.owner,si.idxname, os.state, os2.state "
FROM "informix".systables st, "informix".sysconstraints sc,
"informix".sysindexes si, "informix".sysreferences sr,
"informix".systables rt, "informix".sysconstraints rc,
"informix".sysobjstate os, "informix".sysobjstate os2
WHERE st.tabid = sc.tabid
AND st.tabtype != 'Q'
AND st.tabname NOT MATCHES 'cdr_deltab_[0-9][0-9][0-9][0-9][0-9][0-9]*'
AND rt.tabid = sr.ptabid
AND sc.constrid = sr.constrid
AND sc.tabid = si.tabid
AND sc.idxname = si.idxname
AND sc.constrtype = 'R'
AND st.tabname MATCHES ? -- Remove this line or replace ? with '*' for all tables, tabname for one
AND os.tabid = st.tabid AND os.name = sc.constrname AND os.objtype = 'C'
AND os2.tabid = st.tabid AND os2.name = si.idxname AND os2.objtype = 'I'
AND sr.primary = rc.constrid
ORDER BY si.tabid, sc.constrid ;
------------------------------
Art S. Kagel, President and Principal Consultant
ASK Database Management Corp.
www.askdbmgt.com
------------------------------
Thanks Art, That can help me a lot. Best regards, SP ------------------------------ Sergio Peres AIRC Coimbra ------------------------------