cancel
Showing results for 
Search instead for 
Did you mean: 

SQL objects depencies list

bog
Explorer
0 Kudos

I have a sql server 2008r2 model.

What do I want to do: I want compile all the views/stored procedures scripts in the order dictated by their dependency. In other words if View1 uses View2 then the script for View2 should run before the script for View1.

Is it possible to use PowerDesigner to:

  1. extract such a list of dependencies where I would have 2 columns: object 1, object 2 where for a row with o1, o2 it means o2 is used by o1. Or:
  2. extract the list of all objects ordered by dependency, i.e. objects that depend on other objects will appear in the list after those objects.

If I can't do this easily in PD I am going to use the database catalog views to determine all dependencies but I was hoping it's easy to do in PD.

Thanks

Accepted Solutions (0)

Answers (2)

Answers (2)

Ondrej_Divis
Contributor

Hi,

PD has some algorithms, that should keep the views generated in correct order (btw. my sample was generated in correct order even without traceability links). But if you get incorrect order for any reasen, you can enforce the generation order by using special type of dependency. As you can see from attached picture, I need View_4 to be generated after View_1. So I created new Traceability link on View_4, connected it to View_1 and set the Link type to "DBCreateAfter". The same you can do for Procedures. Then you get DDL generated in correct order for sure.

If you really need output with two columns (object1 and object2) with dependencies correctly captured, you can try creating List report.

1. Report - List report wizard...
2. Object type: select Traceability Links and confirm by Next button
3. In the Column filter check the the Source Object and Linked Object checkboxes (bottom right side of my screenshot)
4. Do nothing in the Row filter dialog and press Next.
5. In the content preview dialog press Finish.
6. Check the newly created List report in your model.

The difference to the list report on View References is that it will get you one list of dependencies including both Views and Procedures.


Regards,

Ondrej

bog
Explorer
0 Kudos

This list report produced indeed what I need - I am impressed!

One observation though, the report didn't include views in the Source Object column. It showed me stored procedures only. Any idea why? Perhaps this is why the View References list is empty.


Thank you!

Ondrej_Divis
Contributor
0 Kudos

You need to understand what the report shows. It is a list of Traceability links in your model. So in my example, the report has exactly 3 rows and it shows both Procedures and Views. Traceability links are PD-only objects, you cannot reverse them from database, you can create them only manually (or by VBscript inside PD). So my List report shows Views not because they read data from another views (and you can see that on the symbol of Views on the diagram), but becase they are connected manually by Traceability link.


If you connect them with View References, you need to create List report based on View References.

GeorgeMcGeachie
Active Contributor

You'll be pleased to hear that the answer is simple - on the Model Menu select "View References" to get a list of view references, which should include the parent and child for you. If you need more information about the parent and child, create a List Report of View References.

bog
Explorer
0 Kudos

This list is empty. Not sure why. After reverse engineering a database, doesn't PD determine the dependencies based on the view source code?

Just to expand here, we have views that reference tables in different databases. I am not even sure how this would work in PD. Does PD allow the modeling of multiple databases in the same model? I was under the impression that it does not. But then how would one represent these dependencies in PD?

Thank you

Ondrej_Divis
Contributor
0 Kudos

You can maintain multiple database in one model (for example by using PD Packages), but you should have serious reasons for that. Each decision of such big-impact kind has both pros and cons. If you put multiple databases into one model, you can have easier life with creating inter-database connections, but on the other hand it could mean bigger models (and slower checkin/checkout when using repository), more difficult parallel work of more users on one model, more HD space consumed for storing new versions, etc. So please choose carefully. My general recommendation is to have more smaller models than few bigger ones. But this is of course general advice without knowing your current situation, requirements, processes, size of the models, etc.