2

I'm tasked to create a SQL statement which will create (and run?) multiple other queries which will select all related data outgoing from an given ID (uniqueidentifier) and a given table name.

It doesn't have to be that fast, but it should be able to use against every db, given only 1 ID and the table name.

In the DB, there are some 1:n, n:m etc. relations and not every table has a column named Id, some only have foreign-keys and it has to be fully dynamically and generic.

I tried to realize it by myself, but I'm not that good in SQL, I also tried some other scripts which will return the primary-foreign-key-relations but I'm stuck there so I'd thought you guys might help me :)

To be returned, I need (would be best) generated queries which I can run against the db and get all information returned (Are there other good return possibilities?)

Alternatively I could create this project in C#, but I'm stuck in attempt as well.

I'll appreciate any kind of help :)

  • 1
    Why does this need to be dynamic? Don't you know the tables and relationships in your database? If not, why not? – Aaron Bertrand Jul 28 '15 at 12:37
  • So, in short, you want to essentially return the source row, plus related rows from other tables for the same record? Correct? Can you guarantee that each table will only have a single unique-identifier column? Or do you know the name of the source column upfront? – Mr.Brownstone Jul 28 '15 at 12:47
  • It needs to be dynamic because we want to be able to apply it to different dbs and different schematas... It's kind of a lightweight db export wich will export only the data we need. "So, in short, you want to essentially return the source row, plus related rows from other tables for the same record? " - correct! I know the name of the column I will start at, and for this column the ID of the row. All related data should be selected outgoing from this field. – probird Jul 28 '15 at 13:03
  • So you need a recursive query that follows all the relationships from that column out to all tables that touches. Is the ID really present in a lot of tables? For example if you start at OrderDetails you can get to Orders based on the ID, but how do you know to get Customers based on Orders.CustomerID, and then Address.Street based on Customers.AddressID, and so on? You need to give a little bit better definition of exactly what relationships are included in your export and which ones do not need to be followed. Especially since some of your tables don't even have Id columns. – Aaron Bertrand Jul 28 '15 at 13:22
  • 1
    Do you need that as a single resultset or as separate tables? – Tom V - Team Monica Jul 29 '15 at 10:42
1

It's not entirely clear what the wanted output is but based on the comments that you want a "lightweight db export" and that the solution needs to be dynamic and "to be able to apply it to different dbs and different schemata", I'll try to make an outline of a solution:

  • Find all FKs in the db in question.
  • Copy all tables to a new db - but without the FKs and no data.
  • Copy the "starting rows" from the db to the copy-db. It doesn't have to be 1 row from 1 table but any number of rows from any number of tables.
  • For each FK, create two statements (one for each direction of the FK) that finds rows in the db that are related to the rows in the copy-db and copies them (counting the copied rows) if they haven't been already copied. This is the dynamic part.
    The normal direction of the FK constraints will be strictly needed (if you want a consistent database as a result) but the reverse direction can be decided by the user performing this procedure (see note below*).
  • Iterate over all these statements.
  • Repeat the iteration until it produces 0 copying (no rows copied at all, in any step).
  • If needed, add the FK constraints in the copy-db. This will be an extra check, that the solution worked and created a usable database.
  • Export the copy-db with a backup or any other method available.

* Explanation:
Lets say they choose a few rows from Orders. The FKs to Customers and Stores are necessary to be followed (and the related rows copied over). For OrderDetails you need the related Products. But the reverse FK routes are not all needed. You'll probably want to have for each customer all the Orders, for each order all the OrderDetails and for each product all the ProductDetails.
But for Products, you'll probably not want all the related OrderDetails.
Because if you do (follow all FKs in both directions), you'll start with a few or a single customer, find all their orders and the related details, then the products ordered, then all the other orders for these products, then all the other customers for these products, then all the other products that these customers ordered, etc. You'll end up, in many cases, with almost the entire database.

  • That's all right, I couldn't have said it better :) – probird Jul 29 '15 at 13:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.