Start a new topic
Answered

Drop tables within the datalocation schema

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.


Best Answer

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 ;


1 Comment

Answer

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 ;


Login to post a comment