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)?
SQL GuruAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ankit PareekOnline MarketingCommented:
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
And how will you know that records in the table are older than one month? All tables have a column timestamp?
0
SQL GuruAuthor Commented:
Yes, it's has datetime column.My problem is getting the  hierarchy.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Vitor MontalvãoMSSQL Senior EngineerCommented:
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.
0
SQL GuruAuthor Commented:
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.
0
SQL GuruAuthor Commented:
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?
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
You can try the following select:
SELECT tc1.TABLE_NAME AS ParentTable, tc2.TABLE_NAME AS ChildTable, tc1.CONSTRAINT_NAME AS PK, tc2.CONSTRAINT_NAME AS FK
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc
	INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc1 ON tc1.CONSTRAINT_NAME = rc.UNIQUE_CONSTRAINT_NAME
	INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc2 ON tc2.CONSTRAINT_NAME = rc.CONSTRAINT_NAME

Open in new window

0
Scott PletcherSenior DBACommented:
Below is a function that will list all tables and their dependency/hierarchy levels using CTE recursion (level 1 = top parent / no children; 2 = 1 parent above; 3 = 2 parents above; etc.).  In this case, you'll obviously want to sort in descending dependency order:

SELECT * FROM dbo.Get_Tables_With_Dependency_Level () ORDER BY dependency_level DESC, schema_name, table_name

CREATE FUNCTION dbo.Get_Tables_With_Dependency_Level ()
RETURNS TABLE
--SELECT * FROM dbo.Get_Tables_With_Dependency_Level () ORDER BY dependency_level DESC, schema_name, table_name
AS
RETURN (
    WITH cte_tables AS
    (
        SELECT
            OBJECT_SCHEMA_NAME(t.object_id) AS schema_name,
            OBJECT_NAME(t.object_id) AS table_name,
            t.object_id AS object_id,
            1 AS dependency_level
        FROM
            sys.tables AS t
        WHERE
            t.is_ms_shipped = 0
        UNION ALL
        SELECT
            OBJECT_SCHEMA_NAME(t.object_id),
            OBJECT_NAME(t.object_id),
            t.object_id,
            ct.dependency_level + 1
        FROM
            sys.tables AS t
        INNER JOIN sys.foreign_keys AS f ON 
            f.parent_object_id = t.object_id AND
            f.parent_object_id != f.referenced_object_id
        INNER JOIN cte_tables AS ct ON 
            f.referenced_object_id = ct.object_id
        WHERE
            t.is_ms_shipped = 0
    )
    SELECT dependency_level, schema_name, table_name, object_id
    FROM (
        SELECT 
            ct.dependency_level,
            ct.schema_name,
            ct.table_name,
            ct.object_id
            , ROW_NUMBER() OVER(PARTITION BY ct.schema_name, ct.table_name ORDER BY ct.dependency_level DESC) AS row_num
        FROM
            cte_tables AS ct
    ) AS derived
    WHERE
        row_num = 1
)
GO --end of function

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
^^^ Nice.  That would be worth building an article..
0
Scott PletcherSenior DBACommented:
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!
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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.
0
Scott PletcherSenior DBACommented:
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.
0
Scott PletcherSenior DBACommented:
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?!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.