How to see the DDL of a table in Asterdatabase

Aster

How to see the DDL of a table in Asterdatabase

Hi Team,

I was trying to look for the table definitions using the SQL but failed to do so..

 

I am aware about act command :  \d <schema> <tablename> but that will just show the components not the definition. L

I know I can have the definition from SQL-administrator or TD studio as well but don’t want to use GUI as I am performing an automation and need the Sql command which can provide me the table definition.

I have also tried the NC_Tables but it didn’t help me much. I have prepared this If you have something better please do share the same..

 

SELECT 10 ,
CAST('CREATE'||' '||tabletype||' TABLE'||' '||schemaname || '.' || tablename
||'( '
AS VARCHAR (100)) as a
FROM NC_SYSTEM.nc_all_tables t
-- INNER JOIN NC_SYSTEM.nc_all_columns c
-- ON c.relid = t.tableid
INNER JOIN NC_SYSTEM.nc_all_schemas s
ON t.schemaid = s.schemaid
WHERE s.schemaname in ('vbp_stg_stb')
AND t.tablename in ('hist_stg_103260_004_data1')

UNION

SELECT 20,
CAST(' , '||c.colname ||' '||c.coltype as VARCHAR(100)) as a
FROM NC_SYSTEM.nc_all_tables t
INNER JOIN NC_SYSTEM.nc_all_columns c
ON c.relid = t.tableid
INNER JOIN NC_SYSTEM.nc_all_schemas s
ON t.schemaid = s.schemaid
WHERE s.schemaname in ('vbp_stg_stb')
AND t.tablename in ('hist_stg_103260_004_data1')

UNION

SELECT 30,
CAST(' )'||' '||
case
when
Trim(t.tabletype)=Trim('fact ')
then 'DISTRIBUTE BY HASH'||' ( '|| c.colname ||' ) '
ELSE CAST(NULL AS VARCHAR(100))
END
|| ' ;' AS VARCHAR(100)) as a

FROM NC_SYSTEM.nc_all_tables t
INNER JOIN NC_SYSTEM.nc_all_columns c
ON c.relid = t.tableid
INNER JOIN NC_SYSTEM.nc_all_schemas s
ON t.schemaid = s.schemaid
WHERE s.schemaname in ('vbp_stg_stb')
AND t.tablename in ('hist_stg_103260_004_data1')
AND c.ispartitionkey = 'True'
ORDER BY s.schemaname, t.tablename,c.colnum ;

ORDER BY 1

Thanks in Advance.