Skip to Content

SQL objects depencies list

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

Add comment
10|10000 characters needed characters exceeded

2 Answers

  • Posted on Mar 12 at 09:01 AM

    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


    generationorder.jpg (596.0 kB)
    Add comment
    10|10000 characters needed characters exceeded

    • 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.

  • Posted on Mar 12 at 08:19 AM

    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.

    Add comment
    10|10000 characters needed characters exceeded

    • 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.