Generate SQL-script for the concrete user

General Questions/Discussions
1

Hi!
Can I generate SQL-script for the concrete user?
For example, I want choose objects of concrete user (I want drop only table USER1.TABLE1, USER1.TABLE2 and don’t drop table USER2.TABLE1, USER2.TABLE2).

2

Hi Eugeny,

Thanks for your post. I guess the USER1, USER2 are schema?
if so, you can sow the schema name on entities by right-click on the diagram and select Presentation Options > Entity Display Options > Show Schema Name in the popup menu. After that, you can multi-select the entities you want to generate SQL script for, right-click on them and select Generate SQL in popup menu. in this way, you can get the SQL script for selected entities.

Hope this helps. Please let me know if I misunderstood your situation, and I appreciate if you would provide more details of your case.

By the way, details about generate SQL script for selected entities can be found from:
http://www.visual-paradigm.com/support/documents/vpumluserguide/208/210/6882_generatingsq.html

Best regards,
Lilian Wong

3

I’m sorry, but I mean that USER1, USER2 are database user’s names. When I update my DB from user USER1 USER2-objects can be deleted. Can I disable view USER2-objects when I generate only for USER1?

4

Hi Eugeny,

I’m sorry that I’m a bit confused. Do you mean USER1.TABLE1, USER2.TABLE1 are same entity (named “TABLE1”) in same database created by different users? Or the entities are actually named “USER1.TABLE1” and “USER2.TABLE1” in the database?

If USER1’s entity relates to USER2’s entity, generate DDL may really affect each other (i.e. USER1’s entity cascades to USER2’s entity). There is no way t ignore any of them since this will violate to the database.

Would you mind to explain more of your case?

Best regards,
Lilian Wong

5

USER1.TABLE1, USER2.TABLE1 are different entity…

6

Hi Eugeny,

May I know what database you are using?

Best regards,
Lilian Wong

7

Oracle 10.2

8

Oracle 10.2

9

Hi Eugeny,

Thanks for replying. I’m sorry that I’m still confused of your USER1.TABLE1, USER2.TABLE1, … Would you mind to send me your DDL so we can understand the case you mentioned?

If you do not want the DDL being accessed by public, please send to lilian.wong@visual-paradigm.com

Best regards,
Lilian Wong

10

DROP TABLE DEVELOPER.YYY_ES1;
DROP TABLE DEVELOPER.YYY_ES2;
DROP TABLE DEVELOPER.YYY_ES3;
DROP TABLE DEVELOPER2.TEMP_TABLE1;
DROP TABLE DEVELOPER2.TEMP_TABLE2;
DROP TABLE DEVELOPER2.TEMP_TABLE3;

So, tables YYY_ES1-YYY_ES3 will deleted, but we consider only object for DEVELOPER2.

11

Hi Eugeny,

Thanks for the sample. As I understand, “DEVELOPER” and “DEVELOPER2” are default schema associated to users of database.

When the database is reversed to VP, the reversed entities will be in different schema “DEVELOPER” and “DEVELOPER2”. This means you can show the schema name of entities on the ERD, multi-select the entities you need and generate SQL from them. Then you will get the drop scripts in the Generate SQL dialog - attached image is an example for generate DROP script for tables of “DEVELOPER” while tables of “DEVELOPER2” are not involved.

Please let me know if there is any question.

Best regards,
Lilian Wong

generate_drop_script_for_specific_tables.png664×1200 57.5 KB

generate_drop_script_for_specific_tables.png

12

Hi!
Can you make such checkbox (please, see attachment) for generate only connected user scheme?

_database_code_generation_options.png800×717 50.6 KB

_database_code_generation_options.png

13

Hi Eugeny,

Thanks for replying and I’ve passed your request to our engineering to investigate the possibility to add this option. I’ll let you know once there is any news on this issue.

Best regards,
Lilian Wong