0 votes

Hello,

In one of my project, my customer create a custom entity into the designer that we really need to destroy before going live.

I need to destroy all fields, tables, relation tables, forms, ... linked to this entity. There no data inside and no code mention it into Efficy or into the custom.

How to do it properly?

Thanks in advance for your help.

Laurent

asked in Database by (120 points)

3 Answers

+3 votes
Best answer

Hi Laurent

With the following SQL (DB=MS) you should be able to delete the custom from the system tables and drop the DB objects. Replace the 50 by the key of the entity you want to delete.

Double check if you have a backup before executing this statements!

First, drop the views and tables related to the entity. With the SQL below, you can generate the drop statements.

declare @K_ENTITY int, @NAME nvarchar(32), @TABLENAME nvarchar(24);
select @K_ENTITY = 50;
select @NAME=NAME from SYS_ENTITIES where K_ENTITY=@K_ENTITY;
select 'drop view R_' + NAME from SYS_TABLES where ROUND(K_TABLE, -3)/1000=@K_ENTITY;
select 'drop table ' + NAME from SYS_TABLES where ROUND(K_TABLE, -3)/1000=@K_ENTITY;

Now proceed with the system tables cleanup. Note that the order of execution is important.

declare @K_ENTITY int, @NAME nvarchar(32), @TABLENAME nvarchar(24);
select @K_ENTITY = 50;
select @NAME=NAME from SYS_ENTITIES where K_ENTITY=@K_ENTITY;

delete from SYS_QUERIES where K_MASTER=@K_ENTITY or K_DETAIL=@K_ENTITY;
delete from SYS_RELATIONS where K_TABLE1=@K_ENTITY*1000;
delete from SYS_RELENTITIES where ROUND(K_TABLE, -3)/1000=@K_ENTITY;

delete from SYS_FIELDS where K_TABLE in (select K_TABLE from SYS_TABLES where ROUND(K_TABLE, -3)/1000=@K_ENTITY)
delete from SYS_TABLES where ROUND(K_TABLE, -3)/1000=@K_ENTITY;
delete from SYS_SECTFIELDS where K_SECTION in (select K_SECTION from SYS_SECTIONS where K_FORM in (select K_FORM from SYS_FORMS where K_TABLE=@K_ENTITY*1000));
delete from SYS_SECTIONS where K_FORM in (select K_FORM from SYS_FORMS where K_TABLE=@K_ENTITY*1000);
delete from SYS_FORMS where K_TABLE=@K_ENTITY*1000;

delete from SYS_ENTITIES where K_ENTITY=@K_ENTITY;
delete from SYS_TABLEVIEWS where ROUND(K_TABLE, -3)/1000=@K_ENTITY;
delete SYS_ENTITYVIEWS where NAME like '%' + @NAME + '%';
Update SYS_ENTITYVIEWS set TABLEVIEWS=replace(TABLEVIEWS, ';' + right(TABLEVIEWS, charindex(';', reverse(TABLEVIEWS) + ';') - 1), '') where TABLEVIEWS like '%' + @NAME;
answered by (7.4k points)
selected by
0 votes

In my opinion you have to restore a database before the customer created this entity

because it s so much a pain in the foot... to remove it entirely.

just restore an ancient backup and import the differential data.

Good luck.

answered by (989 points)
Unfortunately, it is not a possible solution.
0 votes

Hello Laurent,

In my opinion, the real question is not "how" but "when".

When you'll go to production (or your colleges :p ), they can create a "dbStaging".
In the details of the dbStaging, in the SYS.sql / QUERIES.sql ans Structure.sql, you can find every insert related to your custom entities.

Just "delete" them from the SQL file and you'll go to prod without them.

There are, in my knowledge, unfortunately no other way.

Regards,

Loïc

answered by (531 points)
1,249 questions
1,519 answers
1,859 comments
328 users