How to Generate Teradata Object DDL? – Tables,Views,UDF,SP,Macros

  • Post author:
  • Post last modified:October 8, 2021
  • Post category:General
  • Reading time:8 mins read

Teradata database is one of the widely used relational databases. The Teradata system is available on premises and cloud version. Teradata supports many useful tools such as Fast Export, Fast Load, BTEQ, etc. But, there are no tools to pull object DDL’s from the Teradata database. In this article, we will check one of the administrative tasks, How to Generate Teradata Object DDL using System Tables and show command. We will generate DDL’s for Teradata objects such as tables, views, stored procedures, macros and user defined functions.

How to Generate Teradata Object DDL?

Not all the Teradata object DDL’s are available in system tables. DDL’s for the objects such as tables and views are available in DBC.Tables system table or DBC.TablesV system view.

Pull or Generate Teradata Create Tables DDL

Teradata captures and stored create table DDL’s in the DBC.Tables system table. You can query this table to generate table DDL for all tables.

Following query allows you to get CREATE TABLE statement from Teradata system table.

SELECT  RequestText (TITLE '')
FROM    DBC.Tables
WHERE   TableKind = 'T'
AND     upper(DatabaseName) IN ('TEST_DB');

Generate Teradata Create Views DDL

Teradata captures and stored create view DDL’s in the DBC.Tables or DBC.TablesV system table. You can query this table to generate view DDL for all tables.

Following query allows you to get CREATE VIEW statement from Teradata system table.

SELECT  RequestText (TITLE '')
FROM    DBC.TablesV
WHERE   TableKind = 'V'
AND    upper(DatabaseName) IN ('TEST_DB');

Generate Teradata Create Stored Procedure DDL

Teradata won’t store create procedure statement in any system tables. You need to use SHOW PROCEDURE command to get stored procedure DDL’s.

Following shell script allows you to pull create stored procedure DDL from the database.

#!/bin/sh

# Teradata system ID and credentials
tpdtd=192.168.100.20
username=dbc
password=dbc

# list of databases
databases="'DS_DB', 'TEST_DB'"

curr_dir=`pwd`

sp_list=${curr_dir}/"${tpdtd}"_sp_list.txt
sp_ddl=${curr_dir}/"${tpdtd}"_sp_DDL.txt

###########################################################################
# Extract DDL for Procedures
###########################################################################
bteq << EOF
.logon ${tpdtd}/${username},${password};
.SET TITLEDASHES OFF
.SET ECHOREQ OFF;
.SET RECORDMODE OFF;
.SET FORMAT OFF;
.SET PAGEBREAK OFF;
.width 200000
.SET SEPARATOR '|';

.EXPORT REPORT FILE = ${sp_list};

.SET PAGEBREAK OFF;

SELECT  T.DatabaseName ||'.' || T.TableName (TITLE '')
FROM    DBC.TablesV T
WHERE  T.TableKind in ('P', 'E') and upper(DatabaseName) IN (${databases});		
.EXPORT RESET
.logoff;
.quit;
EOF

ReturnCode=$?

if [[ ${ReturnCode} -eq 0 ]]; then
        echo "BTEQ script to extract procedure list completed successfully"
else
        echo "BTEQ script failled"
		rm ${sp_list}
        exit 1
fi

while read func; do
bteq << EOF
.logon ${tpdtd}/${username},${password};
.SET TITLEDASHES OFF
.SET ECHOREQ OFF;
.SET RECORDMODE OFF;
.SET FORMAT OFF;
.SET PAGEBREAK OFF;
.width 200000
.SET SEPARATOR '|';

.EXPORT REPORT FILE = ${sp_ddl};

.SET PAGEBREAK OFF;

SHOW PROCEDURE ${func};
.EXPORT RESET
.logoff;
.quit;
EOF
done < ${sp_list}

ReturnCode=$?

if [[ ${ReturnCode} -eq 0 ]]; then
        echo "BTEQ script to extract procedure DDLs completed successfully"
		rm ${sp_list}
else
        echo "BTEQ script failled"
		rm ${sp_list}
        exit 1
fi

Script will generate the stored procedure DDL’s in current directory.

Generate Teradata Create Function DDL

Teradata won’t store create function statement in any system tables. You need to use SHOW FUNCTION command to get stored procedure DDL’s.

Following shell script allows you to pull create UDF DDL from the Teradata database.

#!/bin/sh

# Teradata system ID and credentials
tpdtd=192.168.100.20
username=dbc
password=dbc

# list of databases
databases="'DS_DB', 'TEST_DB'"

curr_dir=`pwd`

udf_list=${curr_dir}/"${tpdtd}"_udf_list.txt
udf_ddl=${curr_dir}/"${tpdtd}"_udf_DDL.txt

###########################################################################
# Extract DDL for Functions
###########################################################################
# Generate Table DDLs
bteq << EOF
.logon ${tpdtd}/${username},${password};
.SET TITLEDASHES OFF
.SET ECHOREQ OFF;
.SET RECORDMODE OFF;
.SET FORMAT OFF;
.SET PAGEBREAK OFF;
.width 200000
.SET SEPARATOR '|';

.EXPORT REPORT FILE = ${udf_list};

.SET PAGEBREAK OFF;

SELECT  DatabaseName ||'.' || FunctionName (TITLE '')
FROM    DBC.FunctionsV T
WHERE   upper(DatabaseName) IN (${databases});
.EXPORT RESET
.logoff;
.quit;
EOF

ReturnCode=$?

if [[ ${ReturnCode} -eq 0 ]]; then
        echo "BTEQ script to extract function list completed successfully"
else
        echo "BTEQ script failled"
		rm ${udf_list}
        exit 1
fi

while read func; do
bteq << EOF
.logon ${tpdtd}/${username},${password};
.SET TITLEDASHES OFF
.SET ECHOREQ OFF;
.SET RECORDMODE OFF;
.SET FORMAT OFF;
.SET PAGEBREAK OFF;
.width 200000
.SET SEPARATOR '|';

.EXPORT REPORT FILE = ${udf_ddl};

.SET PAGEBREAK OFF;

SHOW FUNCTION ${func};
.EXPORT RESET
.logoff;
.quit;
EOF
done < ${udf_list}

ReturnCode=$?

if [[ ${ReturnCode} -eq 0 ]]; then
        echo "BTEQ script to extract function DDLs completed successfully"
		rm ${udf_list}
else
        echo "BTEQ script failled"
		rm ${udf_list}
        exit 1
fi

Script will generate the user defined function DDL’s in current directory.

Generate Teradata Create Macro DDL

Teradata won’t store create macro statement in any system tables. You need to use SHOW MACRO command to get stored procedure DDL’s.

Following shell script allows you to pull create macro DDL from the database.

#!/bin/sh

# Teradata system ID and credentials
tpdtd=192.168.100.20
username=dbc
password=dbc

# list of databases
databases="'DS_DB', 'TEST_DB'"

curr_dir=`pwd`

macro_list=${curr_dir}/"${tpdtd}"_macro_list.txt
macro_ddl=${curr_dir}/"${tpdtd}"_macro_DDL.txt

###########################################################################
# Extract DDL for Procedures
###########################################################################
bteq << EOF
.logon ${tpdtd}/${username},${password};
.SET TITLEDASHES OFF
.SET ECHOREQ OFF;
.SET RECORDMODE OFF;
.SET FORMAT OFF;
.SET PAGEBREAK OFF;
.width 200000
.SET SEPARATOR '|';

.EXPORT REPORT FILE = ${sp_list};

.SET PAGEBREAK OFF;

SELECT  T.DatabaseName ||'.' || T.TableName (TITLE '')
FROM    DBC.TablesV T
WHERE  T.TableKind in ('P', 'E') and upper(DatabaseName) IN (${databases});		
.EXPORT RESET
.logoff;
.quit;
EOF

ReturnCode=$?

if [[ ${ReturnCode} -eq 0 ]]; then
        echo "BTEQ script to extract procedure list completed successfully"
else
        echo "BTEQ script failled"
		rm ${sp_list}
        exit 1
fi

while read func; do
bteq << EOF
.logon ${tpdtd}/${username},${password};
.SET TITLEDASHES OFF
.SET ECHOREQ OFF;
.SET RECORDMODE OFF;
.SET FORMAT OFF;
.SET PAGEBREAK OFF;
.width 200000
.SET SEPARATOR '|';

.EXPORT REPORT FILE = ${sp_ddl};

.SET PAGEBREAK OFF;

SHOW PROCEDURE ${func};
.EXPORT RESET
.logoff;
.quit;
EOF
done < ${sp_list}

ReturnCode=$?

if [[ ${ReturnCode} -eq 0 ]]; then
        echo "BTEQ script to extract procedure DDLs completed successfully"
		rm ${sp_list}
else
        echo "BTEQ script failled"
		rm ${sp_list}
        exit 1
fi

Script will generate the Teradata macro DDL’s in current directory.

Related Articles,

Hope this helps 🙂

Tags: , , , ,