query

  • Hi,

    Trying to Create a query that will execute on the current database and return information needed for purge logic.

    Columns are:

    1) Table and Schema name

    2) Priority (int) by increments of 1000 starting at 100,000

    3) Foreign key name

    4) Related table name

    5) Has DVD (Is there a field with the word "DVD" in the name. )

    6) Has EmpId (Is there a field called Emp_id or EmpId, employee_id or employeeid)

    7) Has Partner (Is there a field with the word "partner" in the name)

    8) Excluded (a table that not specific to a partner, company or employee is excluded)

    Priority should look something like this (within each priority group sort by table name):

    1) Foreign key dependencies (child tables on top, parent tables on bottom)

    2) Tables with an EmpId

    3) Tables without a EmpId but have an DVD

    4) Tables with partner

    5) All others

    Notes:

    1) System tables should be excluded from this list. Only user tables.

    2) Tables without a relationship add 500,000 to priority so we have room to add more tables with foreign keys

    3) Since Excluded table are not going to be updated add 1,000,000 to the priority so we have room to add more tables.

    The goal is to give highest priority to most dependent tables so that we can delete information in a proper order.

     

    Any suggestions please.

     

    Thank you!

  • You have all the suggestions written out in your post.  Just code to those.  Like Granny used to say, "You can only peel one potato at a time".  Pick a task from the list and write code to solve it.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • This question appears to be a continuation/extension of this one.

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply