schema for views
The poster asked how to get view definitions out of dbschema (IDS 7.3 and 9.4), since a plain dbschema run seemed to show only tables, procedures and synonyms. Replies noted that dbschema -d <db> (optionally with -ss) does include views, and that dbschema -d <db> -t <name> works when <name> is a view. The poster then clarified they wanted, for one table, the SQL of all dependent views without dumping a 10,000-table database; no dbschema option does this. Suggested workarounds: query systables/sysviews matching the table name in viewtext, or use Server Studio's "DB Object Reference Search". No single-command solution was found.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
When i use 'dbschema' i only get the schema for tables, procedures and
synonyms. how can i get the schema for views ( without querying the sys tables)
i m using IDS 7.3 and 9.4 both.
I've received the view information when using dbschema -d database -ss.
**************************************
Ernie Knox
Sears Holding Co.
IT Database Administrator Specialist
IT Service Management, Strategy & Architecture
3333 Beverly Rd., B4-266A
Hoffman Estates, IL. 60179
Office: (847) 286-5735
Fax: (847) 645-3874
Pager: (800) 759-8352 Pin#: 7271042
Email: eknox@sears.com
" It's always a great day to watch Football ! "
**************************************
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of VARTIKA
AGRAWAL
Sent: Saturday, October 20, 2007 4:50 AM
To: ids@iiug.org
Subject: schema for views [10222]
When i use 'dbschema' i only get the schema for tables, procedures and
synonyms. how can i get the schema for views ( without querying the sys
tables)
i m using IDS 7.3 and 9.4 both.
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
dbschema -d <database_name> also gives the schema for
views.
--- VARTIKA AGRAWAL <vartika_agrawal@ril.com> wrote:
> When i use 'dbschema' i only get the schema for
> tables, procedures and
> synonyms. how can i get the schema for views (
> without querying the sys
> tables)
> i m using IDS 7.3 and 9.4 both.
>
>
>
*******************************************************************************
>
> Forum Note: Use "Reply" to post a response in the
> discussion forum.
>
>
__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
i want schema for a single table.
dbschema -t <your table name> -d <your database name>
For more information see the help of "dbschema".
-Shesh
"VARTIKA AGRAWAL" <vartika_agrawal@ril.com>
Sent by: ids-bounces@iiug.org
22/10/2007 11:34
Please respond to
ids@iiug.org
To
ids@iiug.org
cc
Subject
Re: RE: schema for views [10225]
i want schema for a single table.
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
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.
Please use this :
dbschema -d <database name> -t <table name>
Thanks..
Amitava
"VARTIKA AGRAWAL"
<vartika_agrawal@
ril.com> To
Sent by: ids@iiug.org
ids-bounces@iiug. cc
org
Subject
Re: RE: schema for views [10225]
22/10/2007 11:34
Please respond to
ids@iiug.org
i want schema for a single table.
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
Hi,
There is no equivalent to what you want to have as output.
The only chance I see is to select the connected tables using something
like:
select tabname from systables
where tabid in (select tabid from sysviews
where viewtext matches "*tabname*")
Marcus
-----Original Message-----
From: VARTIKA AGRAWAL [mailto:vartika_agrawal@ril.com]
Sent: Monday, October 22, 2007 9:36 AM
To: ids@iiug.org
Subject: Re: RE: schema for views [10227]
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.
Server Studio has a function called "DB Object Reference Search". I will give
you just about what you asked for.
Regards,
Rolf
------- Original Message -------
Subject: Re: RE: schema for views [10227]
From: VARTIKA AGRAWAL (vartika_agrawal@ril.com)
To: ids@iiug.org
Sent: 2007-10-22 09:36
From: vartika_agrawal@ril.com
To: ids@iiug.org
Date: Mon, 22 Oct 2007 03:36:27 -0400
Reply-To: ids@iiug.org
Subject: Re: RE: schema for views [10227]
Message-Id: <20071022073627.23A63A17B@perform.iiug.org>
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.
dbschema -d database_name -t table_name -ss
table_name can be the name of a table or view ...
"VARTIKA AGRAWAL" <vartika_agrawal@ril.com>
Sent by: ids-bounces@iiug.org
10/22/2007 02:04 AM
Please respond to
ids@iiug.org
To
ids@iiug.org
cc
Subject
Re: RE: schema for views [10225]
i want schema for a single table.
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.