הבלוג של גרי רשף

15/06/2010

Identifying Related Tables

Filed under: Uncategorized — תגיות: , , , , , — גרי רשף @ 08:45

לפני מספר שבועות איציק בן-גן פרסם אתגר שזו כותרתו, כשהרעיון הוא למצוא את כל הטבלאות המקושרות במישרין או בעקיפין לטבלה מסויימת בעזרת CTE רקורסיבי.
טרחתי ופתרתי, ואף זכיתי לכך שהפתרון שלי זכה להתייחסות מלומדת בפוסט ההמשך עם הפתרונות, בבחינת- אין אדם אשר אין לו שעה..
רצה הגורל והאתגר התפרסם סמוך לפרסום הפוסט שלי על העץ הפורש המינימלי, והאתגר של איציק כלל למעשה חיפוש של עץ פורש – לאו דווקא מינימלי – בתוך גרף.
כדי שאוכל להתרפק עוד מעט על זרי הדפנה- כתבתי פונקציה שמתמודדת עם האתגר באופן איטרטיבי, כלומר- על ידי לולאה. בדרך כלל פתרונות שאינם Set Based נחשבים לנחותים על ידי משגיחי הכשרות של ה-SQL, אך לעיתים זה בגדר הכרח בל יגונה, ואני מציג את הפתרון הזה כאן גם כדי להדגים Inline Table-Valued Function שמאחורי הקלעים נעזרת בלולאה, אבל מחזירה סט..
אגב- ניסיתי להיעזר ברקורסיה, אבל זה לא הולך.. כלומר- פונקציה שמחזירה טבלה אינה יכולה להיות רקורסיבית ולפנות לעצמה (לבצע Join בינה לבין טבלת התלויות); ולכל היותר היא יכולה לכלול CTE רקורסיבי.

יצירת הטבלאות המקושרות כפי שהיא מופיעה באתגר:

Create Database testfk;

Go

 

USE testfk;

Go

 

CREATE TABLE dbo.D(d INT PRIMARY KEY);

CREATE TABLE dbo.E(e INT PRIMARY KEY, d INT REFERENCES dbo.D);

CREATE TABLE dbo.A(a INT PRIMARY KEY, e INT REFERENCES dbo.E, aa INT REFERENCES dbo.A);

CREATE TABLE dbo.C(c INT PRIMARY KEY);

CREATE TABLE dbo.B(b INT PRIMARY KEY, c INT REFERENCES dbo.C);

CREATE TABLE dbo.AB(a INT REFERENCES dbo.A, b INT REFERENCES dbo.B, PRIMARY KEY(a, b));

 

CREATE TABLE dbo.G(g INT PRIMARY KEY);

CREATE TABLE dbo.F(f INT PRIMARY KEY, g INT REFERENCES dbo.G);

יש להציג את כל הטבלאות המקושרות במישרין או בעקיפין ל-E, כלומר- A, B, C, D, E, AB,

לא להתבלבל בגלל A שמקושרת לעצמה ועלולה להכניס אותנו ללולאה אינסופית,

לא להחזיר דבר אם מבקשים את כל הטבלאות המקושרות לטבלה שאינה קיימת (נניח- X),

וכך הלאה.

הפונקציה:

Create Function dbo.F_Rel(@name Varchar(max))

Returns @T Table(object_id Int,

                id_name Varchar(max))

As

Begin

Insert Into @T

Select    SD.parent_object_id,

        O1.name parent_object_name

From    sys.foreign_key_columns SD

Inner Join sys.objects O1

        On SD.parent_object_id=O1.object_id

Where    O1.name=Isnull(@name,O1.name)

Union

Select    SD.referenced_object_id,

        O2.name

From    sys.foreign_key_columns SD

Inner Join sys.objects O2

        On SD.referenced_object_id=O2.object_id

Where    O2.name=Isnull(@name,O2.name);

While @@RowCount>0

    Insert Into @T

    Select    SD.referenced_object_id,

            O1.name

    From    @T T

    Inner Join sys.foreign_key_columns SD

            On T.object_id=SD.parent_object_id

    Inner Join sys.objects O1

            On SD.referenced_object_id=O1.object_id

    Where    Not Exists (Select    *

                        From    @T T1

                        Where    T1.object_id=SD.referenced_object_id)

    Union

    Select    SD.parent_object_id,

            O2.name

    From    @T T

    Inner Join sys.foreign_key_columns SD

            On T.object_id=SD.referenced_object_id

    Inner Join sys.objects O2

            On SD.parent_object_id=O2.object_id

    Where    Not Exists (Select    *

                        From    @T T1

                        Where    T1.object_id=SD.parent_object_id);

Return

End

Go

שליפת כל הטבלאות המקושרות ל-E:

Select * From dbo.F_Rel('E')

ושליפת כל הטבלאות המקושרות לאחרות:

Select * From dbo.F_Rel(Null)

מודעות פרסומת

להגיב »

עדיין אין תגובות.

RSS feed for comments on this post. TrackBack URI

כתיבת תגובה

הזינו את פרטיכם בטופס, או לחצו על אחד מהאייקונים כדי להשתמש בחשבון קיים:

הלוגו של WordPress.com

אתה מגיב באמצעות חשבון WordPress.com שלך. לצאת מהמערכת / לשנות )

תמונת Twitter

אתה מגיב באמצעות חשבון Twitter שלך. לצאת מהמערכת / לשנות )

תמונת Facebook

אתה מגיב באמצעות חשבון Facebook שלך. לצאת מהמערכת / לשנות )

תמונת גוגל פלוס

אתה מגיב באמצעות חשבון Google+ שלך. לצאת מהמערכת / לשנות )

מתחבר ל-%s

בלוג בוורדפרס.קום.

%d בלוגרים אהבו את זה: