Identifying GTT or volatile tables created during a session

Database
Enthusiast

Identifying GTT or volatile tables created during a session

How can I identify what are the GTT or volatile tables created during a session?

3 REPLIES
Enthusiast

Re: Identifying GTT or volatile tables created during a session

For VT you can use

help volatile table

Also you can read the lines:

To obtain a list of all global temporary tables in the system, query the CommitOpt column of the DBC.TablesV view. For example:

SELECT * FROM DBC.TablesV WHERE CommitOpt IN ('D', 'P');

The CommitOpt column shows the value D or P for a global temporary table. (P is for ON COMMIT PRESERVE ROWS and D is ON COMMIT DELETE ROWS. N indicates the object is not a temporary table.)

To obtain a list of all global temporary tables you own, query the restricted (DBC.TablesVX) view.

You can also query the TransLog column to determine if there is transaction logging for a global temporary table. For more information, see Utilities.

Senior Apprentice

Re: Identifying GTT or volatile tables created during a session

Materialized GTT within your session are listed in dbc.AllTempTablesVX

Enthusiast

Re: Identifying GTT or volatile tables created during a session

realize this is an old thread, but how do i find a VT within a stored procedure

 

eg i have a SP drop_table(db_name, table_name) that i want to drop any type of table (normal, GTT or VT).  it looks in dbc.tables for the first 2 types and drops them if they exist.  Is there anywhere I can look for VT?

 

Thanks