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 (
CustomerIDINTNOT NULL,
CustomerNameVARCHAR (100)NOT NULL,
CustomerStreetVARCHAR (200)NOT NULL,
CustomerCityVARCHAR (100)NOT NULL,
CustomerStateVARCHAR (50)NOT NULL,
CustomerZipVARCHAR (10)NOT NULL,
CustomerEmailVARCHAR (100)NOT NULL,
CustomerPhoneVARCHAR (20)NOT NULL,
CustomerWebSiteVARCHAR (100)NULL
);
ALTER TABLE dbo.Customers
ADD CONSTRAINT pkCustomerID PRIMARY KEY CLUSTERED (CustomerID);
CREATE TABLE dbo.Orders (
OrderIDINTNOT NULL,
OrderCreatedDateDATETIME2NOT NULL,
OrderCreatedByVARCHAR (50)NOT NULL,
OrderLastModifiedDATETIME2NOT NULL,
CustomerIDINTNOT 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 (
OrderItemIDINTNOT NULL,
ItemIDINTNOT NULL,
ItemOrderQuantityINTNOT NULL,
OrderIDINTNOT 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 (
OrderItemShipmentIDINTNOT NULL,
ShipmentTypeINTNOT NULL,
OrderItemIDINTNOT 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 (
OrderItemExceptionIDINTNOT NULL,
ExceptionTypeINTNOT NULL,
OrderItemIDINTNOT 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