All Forums Aster
shukla2502 3 posts Joined 07/13
20 May 2015
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.

You must sign in to leave a comment.