DBC.TABLES has truncated view defination in Requesttext field . How can i find full View defination .

Database
Teradata Employee

DBC.TABLES has truncated view defination in Requesttext field . How can i find full View defination .

Hi,

I am try to find the views that don't have lock on tables in it. But when i try to find defination of these view from dbc.tables, view defination is not complete in requesttext field , its getting truncated.

Can anybody help . How can i find full defination from dbc.tables.

Thanks in Advanc

Sandeep.

13 REPLIES
Enthusiast

Re: DBC.TABLES has truncated view defination in Requesttext field . How can i find full View defination .

Even DBC.TableText  or DBC.tvm  ?

Enthusiast

Re: DBC.TABLES has truncated view defination in Requesttext field . How can i find full View defination .

Use dbc tables to extract the table list and prepare the list of "show view" statements. 

Set the sql assistant to export data and also check the option of "export all the resultset to single file". 

Run the statements at once and all the definitions are exported to single file. 

Teradata Employee

Re: DBC.TABLES has truncated view defination in Requesttext field . How can i find full View defination .

Hi Raja,

Thanks for your comment,

I am able to find it in dbc.tabletext view that is on dbc.texttbl. 

can you please tell about the texttype column values C,R in dbc.texttbl and what these values are refer to ?

Thanks- Sandeep.

Enthusiast

Re: DBC.TABLES has truncated view defination in Requesttext field . How can i find full View defination .

If I am not wrong:  CreateTxtOverFlow = 'C' or RequestTxtOverFlow = 'R' . You can check these three tables

DBC.tvm,DBC.texttbl,DBC.dbase, how they are joined

Teradata Employee

Re: DBC.TABLES has truncated view defination in Requesttext field . How can i find full View defination .

Actully , DBC.texttbl table column texttype has two values C,R and the view on this table is DBC.tabletext with condition Texttbl.TextType = 'R' . i am just wana know why this view filter only R type of rows.

It will be great if you can tell what you mean by CreateTxtOverFlow and RequestTxtOverFlow ?

Thanks- Sandeep.

Enthusiast

Re: DBC.TABLES has truncated view defination in Requesttext field . How can i find full View defination .

Maybe this expert's articel helping you? I have not checked it .

http://teradataforum.com/teradata/20060227_164410.htm

Teradata Employee

Re: DBC.TABLES has truncated view defination in Requesttext field . How can i find full View defination .

The request text field is like that and if you do an alter on the table then poof even if the full request text were there it would be gone. :-)

The best option is what Boopathi15 suggested. You can optionally rebuild the ddl from info in the data dictionary. It is real pain and 

Heres an example of a udf I put together to recreate just the partitioning part of the ddl.

REPLACE FUNCTION SYSLIB.get_partition_stmt (stmt VARCHAR(8000)) 
RETURNS VARCHAR(8000)
SPECIFIC SYSLIB.get_partition_stmt
RETURNS NULL ON NULL INPUT
CONTAINS SQL
COLLATION INVOKER
INLINE TYPE 1
RETURN
(CASE WHEN SUBSTRING( OREPLACE(OREPLACE((CASE WHEN SUBSTRING( OREPLACE(OREPLACE(OREPLACE(OREPLACE(OREPLACE(stmt ,'CHECK (',''), '/*02*/',''),'/*01 02 00*/',''),' IS NOT NULL ',''),')AND','),') FROM 1 FOR POSITION( '))' IN OREPLACE(OREPLACE(OREPLACE(OREPLACE(OREPLACE(stmt ,'CHECK (',''), '/*02*/',''),'/*01 02 00*/',''),' IS NOT NULL ',''),')AND','),') )) = '' THEN
SUBSTRING( OREPLACE(OREPLACE(OREPLACE(OREPLACE(OREPLACE(stmt ,'CHECK (',''), '/*02*/',''),'/*01 02 00*/',''),' IS NOT NULL ',''),')AND','),') FROM 1 FOR POSITION( ' /*' IN OREPLACE(OREPLACE(OREPLACE(OREPLACE(OREPLACE(stmt ,'CHECK (',''), '/*02*/',''),'/*01 02 00*/',''),' IS NOT NULL ',''),')AND','),') )) ELSE SUBSTRING( OREPLACE(OREPLACE(OREPLACE(OREPLACE(OREPLACE(stmt ,'CHECK (',''), '/*02*/',''),'/*01 02 00*/',''),' IS NOT NULL ',''),')AND','),') FROM 1 FOR POSITION( '))' IN OREPLACE(OREPLACE(OREPLACE(OREPLACE(OREPLACE(stmt ,'CHECK (',''), '/*02*/',''),'/*01 02 00*/',''),' IS NOT NULL ',''),')AND','),') ))
END),'(RANGE_N','PARTITION BY RANGE_N'),'(CASE','PARTITION BY CASE') FROM 1 FOR 1) = 'P' THEN OREPLACE(OREPLACE((CASE WHEN SUBSTRING( OREPLACE(OREPLACE(OREPLACE(OREPLACE(OREPLACE(stmt ,'CHECK (',''), '/*02*/',''),'/*01 02 00*/',''),' IS NOT NULL ',''),')AND','),') FROM 1 FOR POSITION( '))' IN OREPLACE(OREPLACE(OREPLACE(OREPLACE(OREPLACE(stmt ,'CHECK (',''), '/*02*/',''),'/*01 02 00*/',''),' IS NOT NULL ',''),')AND','),') )) = '' THEN
SUBSTRING( OREPLACE(OREPLACE(OREPLACE(OREPLACE(OREPLACE(stmt ,'CHECK (',''), '/*02*/',''),'/*01 02 00*/',''),' IS NOT NULL ',''),')AND','),') FROM 1 FOR POSITION( ' /*' IN OREPLACE(OREPLACE(OREPLACE(OREPLACE(OREPLACE(stmt ,'CHECK (',''), '/*02*/',''),'/*01 02 00*/',''),' IS NOT NULL ',''),')AND','),') )) ELSE SUBSTRING( OREPLACE(OREPLACE(OREPLACE(OREPLACE(OREPLACE(stmt ,'CHECK (',''), '/*02*/',''),'/*01 02 00*/',''),' IS NOT NULL ',''),')AND','),') FROM 1 FOR POSITION( '))' IN OREPLACE(OREPLACE(OREPLACE(OREPLACE(OREPLACE(stmt ,'CHECK (',''), '/*02*/',''),'/*01 02 00*/',''),' IS NOT NULL ',''),')AND','),') ))
END),'(RANGE_N','PARTITION BY RANGE_N'),'(CASE','PARTITION BY CASE') ELSE 'PARTITION BY('||OREPLACE(OREPLACE((CASE WHEN SUBSTRING( OREPLACE(OREPLACE(OREPLACE(OREPLACE(OREPLACE(stmt ,'CHECK (',''), '/*02*/',''),'/*01 02 00*/',''),' IS NOT NULL ',''),')AND','),') FROM 1 FOR POSITION( '))' IN OREPLACE(OREPLACE(OREPLACE(OREPLACE(OREPLACE(stmt ,'CHECK (',''), '/*02*/',''),'/*01 02 00*/',''),' IS NOT NULL ',''),')AND','),') )) = '' THEN
SUBSTRING( OREPLACE(OREPLACE(OREPLACE(OREPLACE(OREPLACE(stmt ,'CHECK (',''), '/*02*/',''),'/*01 02 00*/',''),' IS NOT NULL ',''),')AND','),') FROM 1 FOR POSITION( ' /*' IN OREPLACE(OREPLACE(OREPLACE(OREPLACE(OREPLACE(stmt ,'CHECK (',''), '/*02*/',''),'/*01 02 00*/',''),' IS NOT NULL ',''),')AND','),') )) ELSE SUBSTRING( OREPLACE(OREPLACE(OREPLACE(OREPLACE(OREPLACE(stmt ,'CHECK (',''), '/*02*/',''),'/*01 02 00*/',''),' IS NOT NULL ',''),')AND','),') FROM 1 FOR POSITION( '))' IN OREPLACE(OREPLACE(OREPLACE(OREPLACE(OREPLACE(stmt ,'CHECK (',''), '/*02*/',''),'/*01 02 00*/',''),' IS NOT NULL ',''),')AND','),') ))
END),'(RANGE_N','PARTITION BY RANGE_N'),'(CASE','PARTITION BY CASE') ||')' END) ;

Here is how to use it:

SELECT 

 DataBaseName

,TABLENAME

,get_partition_stmt (ConstraintText)

FROM

dbc.indexconstraintsV;

It may not be 100% and its ugly but it worked for what I needed to use it for. I think you can see though that its not a pleasant process. 

Here is something I scripted that you may find useful:


-Fred

Teradata Employee

Re: DBC.TABLES has truncated view defination in Requesttext field . How can i find full View defination .

Raja_KT Thanks for the tip I've never used DBC.texttbl 

Teradata Employee

Re: DBC.TABLES has truncated view defination in Requesttext field . How can i find full View defination .

Actually  DBC.tvm has the same problem with RequestText being replaced with Alter statements. I explored this previously.