I need a query to list the table names used in a view.

UDA

I need a query to list the table names used in a view.

I need a query to list the table names used in a view.

eg. if the view is created such as

create view view1 as select tab1.a, tab2.b from tab1 a, tab2;

the query should populate

view Table
____ _____
view1 tab1
view1 tab1

I could find the DDL in dbc.tvm but this has the complete create statement such as createtext = create view view1 as select tab1.a, tab2.b from tab1 a, tab2;

Can anyone help me?
3 REPLIES
Junior Contributor

Re: I need a query to list the table names used in a view.

I wrote this SP a few years ago populating a Global Temp Table, a recursive query should be possible, too.

Depending on what you actually need a
SHOW SELECT * FROM view1
could be sufficient.

Dieter

/***
Returns number of objects (without SPs!) referencing Database_Name.Table_Name.
Returns -1 if Database_Name.Table_Name object doesn't exist.
AccessRights on DBC.tvm and DBC.dbase needed.

WARNING:
For SPs dbc.tvm.CreateText doesn't have a value -> SPs missing.
An object may be missing if dbc.tvm.CreateTxtOverflow is not NULL.
For reliable information better use Metadata Services instead...

Usage:
CALL Object_Usage('dbc', 'dbase', Usage_Count);

SELECT *
FROM gt_Object_Usage
ORDER BY level, 1,2;
***/

DROP TABLE gt_Object_Usage;

CREATE GLOBAL TEMPORARY TABLE gt_Object_Usage(
Database_Name VARCHAR(30) NOT NULL,
Table_Name VARCHAR(30) NOT NULL,
Table_Kind CHAR NOT NULL,
Level INT NOT NULL
) UNIQUE PRIMARY INDEX(Database_Name, Table_Name)
ON COMMIT PRESERVE ROWS;

REPLACE PROCEDURE Object_Usage(
IN DB_Name VARCHAR(30),
IN Object_Name VARCHAR(30),
OUT Usage_Count INT
)
BEGIN
DECLARE cnt, lvl INT;

SELECT -1 INTO :cnt
WHERE NOT EXISTS
(SELECT * FROM dbc.Tables
WHERE DatabaseName = :DB_Name
AND TableName = :Object_Name);

IF ACTIVITY_COUNT = 0 THEN

DELETE FROM gt_Object_Usage;

SET lvl = 1;

INSERT INTO gt_Object_Usage
SELECT
d.DatabaseName,
t.TVMName,
t.TableKind,
:lvl
FROM
DBC.tvm t
JOIN
DBC.dbase d
ON t.DatabaseId = d.DatabaseId
WHERE
CreateText LIKE '% "' || :DB_Name || '"."' || :Object_Name || '"%'
AND
NOT (d.DatabaseName = :DB_Name AND t.TVMName = :Object_Name)
AND
TableKind IN ('V', 'M', 'I', 'G', 'N');

SET cnt = ACTIVITY_COUNT;

WHILE ACTIVITY_COUNT > 0 DO

SET lvl = lvl + 1;

INSERT INTO gt_Object_Usage
SELECT
d.DatabaseName,
t.TVMName,
t.TableKind,
:lvl
FROM
DBC.tvm t
JOIN
DBC.dbase d
ON t.DatabaseId = d.DatabaseId
WHERE
CreateText LIKE ANY
(
SELECT
'% "' || Database_Name || '"."' || Table_Name || '"%'
FROM gt_Object_Usage
WHERE level = :lvl - 1
)
AND
TableKind IN ('V', 'M', 'I', 'G', 'N')
AND
(d.DatabaseName, t.TVMName) NOT IN
(SELECT
Database_Name, Table_Name
FROM gt_Object_Usage);

SET cnt = cnt + ACTIVITY_COUNT;

END WHILE;

END IF;

SET Usage_Count = cnt;

END;

Re: I need a query to list the table names used in a view.

Thx Dieter... I got ask you on show select...

I found that if the view is created such as

create view view1 as select tab1.a, tab2.b from tab1 a, tab2;

it retrieves the create text of the tab1 and tab2... I got few more outputs of create text for the tables like tab3, tab4 which are referencing the tab1 and tab2. Correct me if I'm wrong... Is this due to referential integrity?

Re: I need a query to list the table names used in a view.

can u tell me hw to find out the free space in each amp's