Retrieving a list of tables/fields

[Norman ELton]

How can I run a query that returns a list of tables in a database, or
a list of fields in a table? For instance, in MSSQL, theres the "show
tables" query that retrieves a list of tables.

This must run in regular SQL, so running "help" or "help <table>"
doesn't seem to work, it's restricted to ISQL.

Thank you

Norman Elton

[Michael Leo]

Norman,

You will need to query the system catalogs iitables and iicolumns.

Cheers,

Michael Leo ml...@cariboulake.com Java, Oracle
Caribou Lake Software http://www.cariboulake.com Ingres, JDBC

Specializing in Ingres/Oracle integration and conversion

[Martin Bowes]

Hi Norman.

help [table] works in both isql and sql. But it will only return details
on the tables you have select access to.

If you need details on all tables in the database then the most reliable
option is to query the catalogs. Specifically iitables and iicolumns. For
details on indexes try iiindexes.

Martin Bowes

--
Random Earthworm Jim Quote #12:
Jim - The weed of crime bears bitter fruit!

[Duncan Gardner]

Norman,

help & help table both work in SQL. You're probably missing the "\g" at the
end of the line

Duncan Gardner
DBA Team, Infrastructure Support Group
Western Australian Government Employees Superannuation Board
(08) 9263 4164
All my own views etc ....

> ----------
> From: eln...@hotmail.com[SMTP:eln...@hotmail.com]
> Sent: 22 June 2001 04:46
> To: info-...@ams.org
> Subject: Retrieving a list of tables/fields

>
> How can I run a query that returns a list of tables in a database, or
> a list of fields in a table? For instance, in MSSQL, theres the "show
> tables" query that retrieves a list of tables.
>
> This must run in regular SQL, so running "help" or "help <table>"
> doesn't seem to work, it's restricted to ISQL.
>
> Thank you
>
> Norman Elton
>

========================================================
Standard disclaimer:
Any recipient of this communication acknowledges that:
* the Government Employees Superannuation Board accepts no responsibility
for the contents, nor the validity of this communication; and
* they do not rely on any view given unless it is properly authorised.
========================================================

[Flipper]

I think he means ABF 4gl

Duncan Gardner wrote in message ...

[Information Analyst]

eln...@hotmail.com (Norman ELton) wrote in message news:<a67295c3.01062...@posting.google.com>...

Try this shell-script (if you don't run your ingres db on a unix platform
be inspired by the SQL part):

#!/bin/sh

database=$1
owner=$2

cat <<EOF | sql $database
select
t.table_name as "table",
c.column_sequence as "#",
c.column_name as column,
c.column_datatype as type,
c.column_length as length,
c.column_nulls as "null?",
c.column_defaults as "default?",
c.key_sequence as "key#"
from
iitables t
join iicolumns c
on t.table_name = c.table_name
where t.table_owner='$owner'
order by "table","#";
\p\g\q
EOF

[Pallett, Roy]

This worked fine on our Ingres II 2.0/0001 installation, but it objected to
'as column'. After revising this to 'as col_name' - no problem.

Cheers, Roy.

#!/bin/sh

database=$1
owner=$2

*****************************************************************
Unless expressly stated otherwise, the contents of this
e-mail are confidential, represent only the views of the
sender expressed only to the intended recipient and are
not intended to impose any legal obligation upon
Breckland Council. If you are not the intended recipient,
you are asked to notify the sender and delete the
message as soon as possible.

*****************************************************************