Skip to main content
Question
Asked a question 4 days ago

Can we drop the tables directly from the back-end database? This is something we want to do in dev environment as we have deployed an Entity with wrong datatype for Primary keys and now we are not able to deploy the model when we update the datatype using the alter entity.

Where am I?

In Semarchy Community you can ask and answer questions and share your experience with others!

Peter TseSemarchy Team
Senior Customer Success Consultant at Semarchy

Hi Khaled, yes you can drop tables directly from the back-end database. I provided sample scripts for Oracle, PostgreSQL, and SQL Server. You can take these scripts and modify them to include the schema and entity name. This will then generate the drop statements you will need to execute. 

Oracle 

 select 'drop table <schemaName>.' || table_name || ';' as generated_statements
   from all_tables 
   where owner = '' 
     and table_name not like 'DL_%'
order by substr(table_name,3), table_name 
;

Postgres SQL 

select 'drop table ' || schemaname || '.' || tablename || ';' as generated_statements
   from pg_catalog.pg_tables
  where schemaname = ''
    and tablename not like 'dl_%'      /* Do not truncate any data location system tables. */
    and tablename not like 'ext_%'
    and tablename ilike '%%'     /* ilike performs a case-insensitive comparison.    */
order by substr(tablename,3), tablename
;																																								

SQL Server

SELECT 'DROP TABLE ' + TABLE_NAME + ';' as generated_statements
FROM <schema_name>.INFORMATION_SCHEMA.TABLES  /* amend this to your data location schema */ 
WHERE TABLE_NAME NOT LIKE 'DL_%'                   /* Don't remove these Semarchy system tables starting with DL and EXT */ 
AND   TABLE_NAME NOT LIKE 'EXT_%'
AND   TABLE_NAME LIKE '%%'                  /* amend this line to meet your table */  
ORDER BY SUBSTRING(TABLE_NAME, 1, 3), TABLE_NAME
;
Fathia JamaSemarchy Team
Sr. Customer Success Consultant at Semarchy

Hi Khaled, yes you can drop tables directly from the back-end database. I provided sample scripts for both Oracle and Postgres. You can take these scripts and modify them to include the schema and entity name. This will then generate the drop statements you will need to execute. 

Oracle 

 select 'drop table <schemaName>.' || table_name || ';' as generated_statements
   from all_tables 
   where owner = '' 
     and table_name not like 'DL_%'
order by substr(table_name,3), table_name 
;

Postgres SQL 

select 'drop table ' || schemaname || '.' || tablename || ';' as generated_statements
   from pg_catalog.pg_tables
  where schemaname = ''
    and tablename not like 'dl_%'      /* Do not truncate any data location system tables. */
    and tablename not like 'ext_%'
    and tablename ilike '%%'     /* ilike performs a case-insensitive comparison.    */
order by substr(tablename,3), tablename
;