Archives of the TeradataForum

Message Posted: Fri, 21 Nov 2003 @ 03:28:50 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Hi,

How about trying with this?

---------------------------------------------

REPLACE MACRO dlm_macros.Show_DDL
(indb VARCHAR(30), kinds CHAR(10))

AS

(ECHO '.TITLEDASHES OFF;';'
ECHO '.OMIT ON 1;';

SELECT
CASE
WHEN TableKind='T' THEN 1
WHEN TableKind='V' THEN 2
WHEN TableKind='M' THEN 3
WHEN TableKind='G' THEN 4
WHEN TableKind='I' THEN 5
ELSE 6
END,

CASE
WHEN TableKind='T' AND POSITION('T' IN :kinds) > 0
THEN'SHOW TABLE '||TRIM(DATABASENAME)||'.'||TRIM(TABLENAME)||';'
WHEN TableKind='V' AND POSITION('V' IN :kinds) > 0
THEN
'SHOW VIEW '||TRIM(DATABASENAME)||'.'||TRIM(TABLENAME)||';'
WHEN TableKind='M' AND POSITION('M' IN :kinds) > 0
THEN
'SHOW MACRO '||TRIM(DATABASENAME)||'.'||TRIM(TABLENAME)||';'
WHEN TableKind='G' AND POSITION('G' IN :kinds) > 0
THEN
'SHOW TRIGGER'||TRIM(DATABASENAME)||'.'||TRIM(TABLENAME)||';'
WHEN TableKind='I' AND POSITION('I' IN :kinds) > 0
THEN
'SHOW JOIN INDEX'||TRIM(DATABASENAME)||'.'||TRIM(TABLENAME)
ELSE '/*'||TRIM(DatabaseName)||'.'||TRIM(TableName)||'*/'
END (CHAR(85), TITLE '')

FROM DBC.Tables

WHERE DATABASENAME = :indb

ORDER BY 1;);

---------------------------------------------

Regards,

Manoj

	<Prev	Next>		<<First	<Prev	Next>	Last>>