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
S
Stéphanie FOURRIER
said
over 2 years ago
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
S
Stéphanie FOURRIER
said
over 2 years ago
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 ;
Stéphanie FOURRIER
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.
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.
Stéphanie FOURRIER
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.
-
Deployment History Date
-
Username in Tomcat Access Logs
-
Is It Possible to Perform Automatic Authentication with The User's Windows Account?
-
Where is the documentation for Version 5.2.5?
-
On Prem Semarchy Authentication using Azure AD?
-
Delete old models
-
Sync production model version with dev
-
Recreate a dev environment. Any side effect?
-
Indexes on xDM database tables
-
What logging technology is used in the Semarchy xDM platform?
See all 63 topics