SQL Guru
asked on
Get the table order from child to parent
Hi,
I need to delete data older than one month from all the tables. there is no documentation as such. And there are around 500 tables in the database. Apart from analyzing the database design/diagram, can we get the sequence of table name to be deleted in a order ( child table to parent)?
I need to delete data older than one month from all the tables. there is no documentation as such. And there are around 500 tables in the database. Apart from analyzing the database design/diagram, can we get the sequence of table name to be deleted in a order ( child table to parent)?
And how will you know that records in the table are older than one month? All tables have a column timestamp?
ASKER
Yes, it's has datetime column.My problem is getting the hierarchy.
If the relationships were created with DELETE CASCADE option you'll only need the parent record and the engine will take care to delete all the related children.
ASKER
No, it's not created with cascade option. Moreover, again i will follow into a pit for knowing the parent tables. So, it will great if we can get these details from metadata.
ASKER
Ankit Pareek:
In the blog , there already pre-defined data showing the parent-child relationship in column. But in mycase, how do i determine this mapping?
In the blog , there already pre-defined data showing the parent-child relationship in column. But in mycase, how do i determine this mapping?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
^^^ Nice. That would be worth building an article..
Thanks! I really wish I had the time, it would be fun to write such articles.
But, lol, I just noticed that I used:
OBJECT_NAME(t.object_id)
when I could have simply used:
t.name
instead. D'OH!
But, lol, I just noticed that I used:
OBJECT_NAME(t.object_id)
when I could have simply used:
t.name
instead. D'OH!
I have a list of articles ready to work if/when I have a lot of bench time or time sitting around at kids sports tournaments. Most of my better ideas were largely written in one Starbucks or another.
Besides, the code definitely needs more work before it can be considered really done. I'm almost certain that the code above will "get confused" when the same table appears multiple times at different levels in relationships.
Finally, even that code was quite a bit of work: if I can get 100 pts just by doing simple joins of obsolete views, why go to all the trouble of writing code that complex?!
http://social.technet.microsoft.com/wiki/contents/articles/21062.t-sql-hierarchical-table-sorting-with-a-parent-child-relation.aspx