How to store output of show table in a variable in Stored procedure

Database
Enthusiast

How to store output of show table in a variable in Stored procedure

Hi,

I am writing a stored procedure in which i need to drop a table recreate it and keep the defn of the table in an audit table. So, firstly, i am creating backup table as the original table with no data. then dropping the original table and recreating the original table with backup table. This is quite simple.

another thing that i want to do is to enter details in an audit table.  i want to insert dbname,table and the ddl of the table. so, how do i get ddl of the table.

i can get this from dbc.tables from requesttext column, but i think it gets truncated. the size of this field is 125000, but i see that some of big defn table's ddl are truncated in requesttext. is my understanding correct ?

another option is to get the o/p of show backup table and insert into the audit table. But how do i store the o/p of a show table into a variable and then insert into the audit table ? i cant use a sel col into variable from tablename.

Please help me on this.

--thanks !

Tags (1)
7 REPLIES
Enthusiast

Re: How to store output of show table in a variable in Stored procedure

Hi teradatauser2,

The following query would help you to do the required. But it depends on total size (DDL size) of table. You can merge the datarows and get your table definition. Try using a recursive query or a loop and then store the same in a CLOB datatype. And if possible, please do let me know whether it was done or not.

SELECT
B.DATABASENAME
, A.TVMNAME
, C.TEXTSTRING
FROM
DBC.TVM A
INNER JOIN
DBC.DBASE B
ON A.DATABASEID = B.DATABASEID
INNER JOIN
DBC.TEXTTBL C
ON A.TVMID = C.TEXTID
WHERE
A.TABLEKIND = 'T'
AND B.DATABASENAME = 'YOUR_DATABASE_NAME'
AND A.TVMNAME = 'YOUR_TABLE_NAME';

Thanks,

Rohan Sawant

Senior Apprentice

Re: How to store output of show table in a variable in Stored procedure

RequestText contains the source code of the last modification of a table definition, i.e. ALTER TABLE or CREATE INDEX instead of CREATE TABLE.

Have a look at Glenn McCall's article how to solve this:

Running Unsupported Queries from a Stored Procedure

Enthusiast

Re: How to store output of show table in a variable in Stored procedure

Thanks Diether. The link suggets that i need to install this java UDF. Is there no sql soluton for this as i am not sure if i will be able to get permission to install that udf in my system.

For the other part of your answer, if i do a create table for a new table , does that table ddl doesn't go to requesttext ?

Enthusiast

Re: How to store output of show table in a variable in Stored procedure

Hi teradatauser2,

The sql which i shared above give you the whole table table defination. You just need to concatenate them. Please check.

Thanks,

Rohan Sawant.

Senior Apprentice

Re: How to store output of show table in a variable in Stored procedure

@Rohan:

Please check the code in dbc.TablesV.RequestText after doing any DDL on a table.

@teradatauser2:

There's no way to get the actual source code for a table beside SHOW TABLE.

Btw, why do you drop and recreate the table instead of simply deleting the data?

Enthusiast

Re: How to store output of show table in a variable in Stored procedure


I understand that RequestTest column of dbc.TablesV truncates the definition. But if you check the query which I had provided in my earlier post doesnt use dbc.TablesV. There is a table DBC.TEXTTBL which provides the table definition in different rows in the output which are required to be concatenated in order to get the complete DDL.

for example:

A sample output of the above given query:

   DatabaseName    TVMName                TextString

1 TEST_P               TABLE_NAME1         TextString001.txt

2 TEST_P               TABLE_NAME1         TextString002.txt

3 TEST_P               TABLE_NAME1         TextString003.txt

4 TEST_P               TABLE_NAME1         TextString004.txt

On concatenation of the above TextString*.txt files we will get the complete tables definition

Thanks,

Rohan Sawant

Senior Apprentice

Re: How to store output of show table in a variable in Stored procedure

Hi Rohan,

dbc.TextTbl keeps the full source code if it's more than 12500 characters, but for tables it's still the last DDL, e.g.

SELECT
C.TEXTSTRING
FROM
DBC.TVM A
INNER JOIN
DBC.DBASE B
ON A.DATABASEID = B.DATABASEID
INNER JOIN
DBC.TEXTTBL C
ON A.TVMID = C.TEXTID
WHERE
A.TABLEKIND = 'T'
AND B.DATABASENAME = 'dbc'
AND A.TVMNAME = 'ResUsageSps';

*** Query completed. One row found. One column returned.
*** Total elapsed time was 1 second.

TextString
-------------------------------------------------------------------
ALTER TABLE ResUsageSps ADD QWaitTime FLOAT