query

Question

query

adisql

SSCrazy

Points: 2893
More actions
August 11, 2022 at 1:51 pm

#4075220

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!

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

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

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 1

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)

ratbak Ten Centuries Points: 1333 More actions · Answer 2

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