I need to add starbox comments to table DDL. Works with view DDL, but not table DDL.

Database
Enthusiast

I need to add starbox comments to table DDL. Works with view DDL, but not table DDL.

Hello everyone,

I'm on a new project and one of our requirements is that all DDL objects must be created using a STARBOX in the DDL for tracking purposes.  Here's what this looks like when creating a view:

/*********************************************************************************************************************

**
DATABASENAME - DATABASENAME **

**
OBJECT NAME - AGRIAS123_V **

**
PROJECT - AGRIAS PROJECT **

**
CREATION DATE - 2012-12-19 **

**
INTIAL AUTHOR - AGRIAS, AGRIAS COMPANY **

**
DESCRIPTION - AGRIAS TEST TABLE **

** **

*****************************************
---------- Change Log ---------- ****************************************

**
Change Date Developer Name Change Desc **

**
------------------- ------------------------ -------------------------------- **

**
2012-12-19 AGRIAS INITIAL CREATION **

*********************************************************************************************************************/


CREATE TABLE GEADW_SHOP_S.TESTTLC123

(COLUMN1
VARCHAR(1)

, COLUMN2
INT

, COLUMN3 DECIMAL(3,2))

PRIMARY INDEX (COLUMN2);

So when I create this view, and do a SHOW VIEW DATABASENAME.AGRIAS123_V, I see this starbox in the system.

However, if I use a starbox while creating a TABLE, it does not store in the database.  Doing a SHOW TABLE DATABASENAME.AGRIAS123 will only show the database DDL code.

Does anyone know of a way to get around this?  I tried creating these tables in SQL Assistant and BTEQ - neither worked.  I also tried executing in parallel, and adjusting the driver details to not use extended SQL. Does anyone have any tricks for getting the comments to store in table DDL?

Thanks,

Agrias

3 REPLIES
Junior Contributor

Re: I need to add starbox comments to table DDL. Works with view DDL, but not table DDL.

Hi Agrias,

no, there's no way to keep any comment/formatting within the table DDL.

The output of a SHOW TABLE is not retrieved from any system table, it's created ad-hoc by the parser.

There is the RequestText column in dbc.tvm which holds the source code of the *latest* query modifying the table structure, i.e. when you do a CREATE INDEX/ALTER TABLE, the original CREATE TABLE text (including the comments) is overwritten.

Dieter

Enthusiast

Re: I need to add starbox comments to table DDL. Works with view DDL, but not table DDL.

Thanks for the response Dieter, I'll look into what we can do with DBC.TVM.

Teradata Employee

Re: I need to add starbox comments to table DDL. Works with view DDL, but not table DDL.

Hi Agrias,

I am not sure about the earlier versions, We are using 13.10, dbc.Tables stores last run of the definitions including the starbox comments in Request_Text Column.

Show Request_Text FROM dbc.Tables

WHERE DatabaseName=<Datebase Name>

AND TableName = <TableName>

Hope this would solve your problem.

Br,

Asad