Creating hierarchical metadata based on DMVs or system tables
-
I have a requirement to archive & purge my OLTP data. The database is a tightly defined relational database with Primary Keys/Foreign Keys very thoroughly defined. For this process, I am planning to build hierarchical meta data on fly (before archiving and purging) so that I can go top to bottom while archiving and bottom to top while purging.
For example, below are my sample table details...
I want to create hierarchical metadata for my transactional tables. I envision the following...
With the ranking I have in the "Level" column of the output, I am guessing I can start to copy data (for archiving) from Level going all the way to Level 2. Likewise, for purging I can start purging data from the bottom most level and go all the way to the top.
In an environment like where all the relations are very well defined, I feel that it will work efficiently. For instance, if new tables are created at a later data (assuming their relations are defined as expected), I don't have to make modifications to my archive/purge scripts since the script will build the metadata at the beginning and use for the process.
My first question is, how to build a query uisng DMVs and/or system tables to give me this output? And secondly, is there a better way to archive and purge data where I don't have to change my script everytime new tables are built.
Following is the script to build the tables I cited above.
CREATE TABLE dbo.Customers (
CustomerID INT NOT NULL,
CustomerName VARCHAR (100) NOT NULL,
CustomerStreet VARCHAR (200) NOT NULL,
CustomerCity VARCHAR (100) NOT NULL,
CustomerState VARCHAR (50) NOT NULL,
CustomerZip VARCHAR (10) NOT NULL,
CustomerEmail VARCHAR (100) NOT NULL,
CustomerPhone VARCHAR (20) NOT NULL,
CustomerWebSite VARCHAR (100) NULL
);
ALTER TABLE dbo.Customers
ADD CONSTRAINT pkCustomerID PRIMARY KEY CLUSTERED (CustomerID);
CREATE TABLE dbo.Orders (
OrderID INT NOT NULL,
OrderCreatedDate DATETIME2 NOT NULL,
OrderCreatedBy VARCHAR (50) NOT NULL,
OrderLastModified DATETIME2 NOT NULL,
CustomerID INT NOT NULL
);
ALTER TABLE dbo.Orders
ADD CONSTRAINT pkOrderID PRIMARY KEY CLUSTERED (OrderID);
ALTER TABLE dbo.Orders
ADD CONSTRAINT fkOrdersCustomer
FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID);
CREATE TABLE dbo.OrderItems (
OrderItemID INT NOT NULL,
ItemID INT NOT NULL,
ItemOrderQuantity INT NOT NULL,
OrderID INT NOT NULL
);
ALTER TABLE dbo.OrderItems
ADD CONSTRAINT pkOrderItemID PRIMARY KEY CLUSTERED (OrderItemID);
ALTER TABLE dbo.OrderItems
ADD CONSTRAINT fkOrdersItemOrder
FOREIGN KEY (OrderID) REFERENCES Orders(OrderID);
CREATE TABLE dbo.OrderItemShipment (
OrderItemShipmentID INT NOT NULL,
ShipmentType INT NOT NULL,
OrderItemID INT NOT NULL
);
ALTER TABLE dbo.OrderItemShipment
ADD CONSTRAINT pkOrderItemShipmentID PRIMARY KEY CLUSTERED (OrderItemShipmentID);
ALTER TABLE dbo.OrderItemShipment
ADD CONSTRAINT fkOrderItemsOrderItemsShipment
FOREIGN KEY (OrderItemID) REFERENCES OrderItems (OrderItemID);
CREATE TABLE dbo.OrderItemException (
OrderItemExceptionID INT NOT NULL,
ExceptionType INT NOT NULL,
OrderItemID INT NOT NULL
);
ALTER TABLE dbo.OrderItemException
ADD CONSTRAINT pkOrderItemExceptionID PRIMARY KEY CLUSTERED (OrderItemExceptionID);
ALTER TABLE dbo.OrderItemException
ADD CONSTRAINT fkOrderItemsOrderItemException
FOREIGN KEY (OrderItemID) REFERENCES OrderItems (OrderItemID);
- Rex
-
It is not really possible to map out RI as a hierarchical model as you are discuss. This is because there is nothing that prevents circular references and that sort of thing.
You can make your purging of data painless if you use cascading delete foreign keys. Archiving is going to be a manual process. I just don't think there is any way around it. You will have to update your script when you add new tables.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/ -
Sean,
Thanks for your advise. As of now, I don't have any tables with circular references.
- Rex
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply