Generating DDL file

Toad Data Modeler
1

On another forum I have posted issues that I am having with updating models with the database while this is looked at, I think the process may be too heavy for couple of table updates. Is there an API for generating DDL scripts? I would like to create a macro that by right clicking gives me the option to write the DDL statements and the content related packages.

The scenario would be to
Open Text Stream
Write the current entity DDL (create table, comments, constraints)
Write the associated package (Heavy user of naming conventions)

If I need to write the whole statement piece by piece, I will do it but API would be better.

2

Hi Hazi,

please see this article:
http://blogs.inside.quest.com/modeling/2010/11/09/run-macro-for-selected-entities-and-store-ddl-preview-to-files/

Of course, feel free to modify it to add there code of your associated packages.

Regards,

Vaclav

3

Thanks Vaclav. Modified the script to add corresponding table packages. code() function is great.

Hazi

4

You are welcome.

Vaclav

5

Hi Vaclav,

I am certainly abusing your generous help. Can you also provide me info on how to generate the foreign key statements for the table.

Thanks

6

Hi,

in TDM you can define settings how to generate ref.integrity. Try to generate SQL and see tab Referential Integrity.

I don’t know if you want to generate the code for child entities or parent entities in your script. Below you can see script that uses child entity.

See modified code SaveDLL (full example at http://blogs.inside.quest.com/modeling/2010/11/09/run-macro-for-selected-entities-and-store-ddl-preview-to-files/)

function SaveDDL(Entity)
{
var Log = System.CreateObject(“Log”);
System.RegisterInterface( Log, ‘Log’);
var OutputFile, Content, Relation, RelationCode, i;
var Dialog = System.CreateObject(“SaveDialog”);
Dialog.InitialDir = “C:\”;
Dialog.FileName = Entity.Name + “.sql”;
Dialog.Execute();
OutputFile = new ActiveXObject(“Scripting.FileSystemObject”);
Content = OutputFile.CreateTextFile(Dialog.FileName, true);
Content.WriteLine(Entity.Code());
**// relation DDL (iterated entity is child entity)
for(i=0; i<Entity.Relations.Count; i++)
{
Relation = Entity.Relations.GetObject(i);
// child entity check
if(Relation.ChildEntity == Entity)
{
Content.WriteLine(Relation.Code());
}
} ** Content.Close();
OutputFile = null;
Log.Information("Entity DDL saved to file: " + Dialog.FileName);
}

Tips: PEREntity class has property Relations. See class PERRelation and its properties ParentEntity and ChildEntity.

Regards,

Vaclav