IDS Forum

windows-1252?Q52=65=3A=20=52=45=3A=20=73=63=68=65=

Posted By: ART KAGEL, BLOOMBERG/ 731 LEXIN
Date: Monday, 22 October 2007, at 9:20 a.m.

Download my package, utils2_ak, from the IIUG Software Repository and build my dbschema replacement utility, myschema. It has an option, -F, which prints out dependent objects for a table including views that reference the table and foreign keys on other tables that reference the table. Ex:
> myschema -d sysmaster -t sysdbstab -F
Writing full schema DDL to: stdout
{ TABLE sysdbstab row size = 138 number of columns = 30 index size = 0 }
CREATE TABLE sysdbstab (

address INTEGER,

dbsnum INTEGER,

flags INTEGER,

fchunk INTEGER,

nchunks INTEGER,

created INTEGER,

prtpage INTEGER,

partp INTEGER,

bpagesize INTEGER,

bcolcnt INTEGER,

level0 INTEGER,

stamp0 INTEGER,

logid0 INTEGER,

logpos0 INTEGER,

level1 INTEGER,

stamp1 INTEGER,

logid1 INTEGER,

logpos1 INTEGER,

level2 INTEGER,

stamp2 INTEGER,

logid2 INTEGER,

logpos2 INTEGER,

logid INTEGER,

logpos INTEGER,

oldlogid INTEGER,

lastlogid INTEGER,

rest_time INTEGER,

arc_pit INTEGER,

name CHAR(18),

owner CHAR(8)
)
EXTENT SIZE 16 NEXT SIZE 16 LOCK MODE PAGE;

{

Please review extent sizing and adjust to allow for growth.
}

REVOKE ALL ON sysdbstab FROM public;
CREATE UNIQUE INDEX sysdbstab_dbsnum ON sysdbstab (

dbsnum ASC
);

GRANT SELECT ON sysdbstab TO "public";

CREATE VIEW sysdbspaces (

dbsnum, name, owner, fchunk, nchunks, is_mirrored, is_blobspace,

is_temp, flags)
AS
SELECT

x0.dbsnum ,x0.name ,x0.owner ,x0.fchunk ,x0.nchunks ,bitval(x0.flags

,'0x2'),bitval(x0.flags ,'0x10'),bitval(x0.flags ,'0x2000'),x0.flags

FROM

sysdbstab x0

WHERE

(x0.dbsnum > 0 ) ;

GRANT SELECT, DELETE ON sysdbspaces TO "informix" WITH GRANT OPTION;
GRANT UPDATE (dbsnum) ON sysdbspaces TO "informix" WITH GRANT OPTION;
GRANT UPDATE (fchunk) ON sysdbspaces TO "informix" WITH GRANT OPTION;
GRANT UPDATE (flags) ON sysdbspaces TO "informix" WITH GRANT OPTION;
GRANT UPDATE (name) ON sysdbspaces TO "informix" WITH GRANT OPTION;
GRANT UPDATE (nchunks) ON sysdbspaces TO "informix" WITH GRANT OPTION;
GRANT UPDATE (owner) ON sysdbspaces TO "informix" WITH GRANT OPTION;
GRANT SELECT ON sysdbspaces TO "public";

Art S. Kagel

----- Original Message -----
From: Vartika Agrawal <ids@iiug.org>
To: ids@iiug.org
At: 10/22 3:37:15

Thanks for telling me to refer "dbschema" help.

i want the schema for a single table where i can get the sql for ALL the
dependent objects ie VIEWS , synonyms, sequence , procedure etc.
i get the sql for synonyms, sequence , procedure using
dbschema -d <> -t <> -f all -- for SPL
dbschema -d <> -t <> -s -- for synonyms
dbschema -d <> -t <> -seq -- for sequence.

i want the schema for a single table with the sql for all the views based on
that table.

it not feasible to take dbschema of full database as i have more than 10000
tables in that particular database.

*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.

IDS Forum is maintained by Administrator with WebBBS 5.12.