The T-SQL code in Listing 2 creates a system stored procedure named sp_ExamineChildren. The stored procedure accepts two required parameters, a table name (@DBTable) and a primary key value (@DBValue). The routine returns a result set containing a row for every child table. Each row includes a child table name and the number of rows in the table that relate to the parent table row specified by the parameters. This stored procedure might be used for simple data analysis. A SUM aggregation on the number of rows column can determine whether the parent row has any child rows. If the sum is greater than zero then child rows exist and the parent row must not be deleted.
You can examine the children of a row in the Purchase table with this stored procedure call:
EXECUTE sp_ExamineChildren 'Purchase',3
The query below returns a set of data containing purchase details for a certain customer. The query involves eight tables with a variety of parent/child relationships. The result is a denormalized set of data suitable for a report. However, the result may not be much use to a DBA or developer because the source of each column is not apparent in the output. A problem could easily be concealed among all the redundant data (the same parent data repeated with each child row).
SELECT R.RegionName
, C.Name
, O.OrderNumber
, O.OrderDate
, I.LineNumber
, I.Quantity
, V.Name
, P.Description
, S.Carrier
, S.TrackingNumber
, S.ShipDate
FROM Customer AS C
JOIN Region AS R
ON C.RegionID = R.RegionID
JOIN Purchase AS O
ON C.CustomerID = O.CustomerID
JOIN PurchaseItem AS I
ON O.PurchaseID = I.PurchaseID
JOIN Product AS P
ON I.ProductID = P.ProductID
JOIN Vendor AS V
ON P.VendorID = V.VendorID
JOIN PurchaseItemShipment AS E
ON I.PurchaseItemID = E.PurchaseItemID
JOIN Shipment AS S
ON E.ShipmentID = S.ShipmentID
WHERE C.CustomerID = 2
ORDER BY O.OrderNumber
, I.LineNumber
Click for the stored procedures to examine children
Home: 
