Tjw5M78K

1SET NOCOUNT ON
2
3-- WWB: Create a Temp Table Of All Relationship To Improve Overall Performance
4CREATE TABLE #TableRelationships (FK_Schema nvarchar(max), FK_Table nvarchar(max),
5    PK_Schema nvarchar(max), PK_Table nvarchar(max))
6
7-- WWB: Create a List Of All Tables To Check
8CREATE TABLE #TableList ([Schema] nvarchar(max), [Table] nvarchar(max))
9
10-- WWB: Fill the Table List
11INSERT INTO #TableList ([Table], [Schema])
12SELECT TABLE_NAME, TABLE_SCHEMA
13FROM INFORMATION_SCHEMA.TABLES 
14WHERE Table_Type = 'BASE TABLE'
15
16-- WWB: Fill the RelationShip Temp Table
17INSERT INTO #TableRelationships(FK_Schema, FK_Table, PK_Schema, PK_Table)
18SELECT
19    FK.TABLE_SCHEMA,
20    FK.TABLE_NAME,
21    PK.TABLE_SCHEMA,
22    PK.TABLE_NAME
23FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C
24      INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK ON 
25        C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
26      INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK ON 
27        C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
28      INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU ON 
29        C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
30      INNER JOIN (
31            SELECT i1.TABLE_NAME, i2.COLUMN_NAME
32            FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1
33            INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2 ON
34             i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME
35            WHERE i1.CONSTRAINT_TYPE = 'PRIMARY KEY'
36) PT ON PT.TABLE_NAME = PK.TABLE_NAME
37
38CREATE TABLE #Stack([Schema] nvarchar(max), [Table] nvarchar(max))
39
40GO
41
42-- WWB: Drop SqlAzureRecursiveFind
43IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = 
44    OBJECT_ID(N'[dbo].[SqlAzureRecursiveFind]') AND type in (N'P', N'PC'))
45DROP PROCEDURE [dbo].[SqlAzureRecursiveFind]
46
47GO
48
49-- WWB: Create a Stored Procedure that Recursively Calls Itself
50CREATE PROC SqlAzureRecursiveFind
51    @BaseSchmea nvarchar(max),
52    @BaseTable nvarchar(max),
53    @Schmea nvarchar(max),
54    @Table nvarchar(max),
55    @Fail nvarchar(max) OUTPUT
56AS
57
58    SET NOCOUNT ON
59
60    -- WWB: Keep Track Of the Schema and Tables We Have Checked
61    -- Prevents Looping          
62    INSERT INTO #Stack([Schema],[Table]) VALUES (@Schmea, @Table)
63
64    DECLARE @RelatedSchema nvarchar(max)
65    DECLARE @RelatedTable nvarchar(max)
66
67    -- WWB: Select all tables that the input table is dependent on
68    DECLARE table_cursor CURSOR LOCAL  FOR
69          SELECT PK_Schema, PK_Table
70          FROM #TableRelationships
71          WHERE FK_Schema = @Schmea AND FK_Table = @Table
72
73    OPEN table_cursor;
74
75    -- Perform the first fetch.
76    FETCH NEXT FROM table_cursor INTO @RelatedSchema, @RelatedTable;
77
78    -- Check @@FETCH_STATUS to see if there are any more rows to fetch.
79    WHILE @@FETCH_STATUS = 0
80    BEGIN
81
82        -- WWB: If We have Recurred To Where We Start This
83        -- Is a Circular Reference
84        -- Begin failing out of the recursions
85        IF (@BaseSchmea = @RelatedSchema AND 
86                @BaseTable = @RelatedTable)
87            BEGIN
88                SET @Fail = @RelatedSchema + '.' + @RelatedTable
89                RETURN
90            END
91        ELSE            
92        BEGIN
93
94            DECLARE @Count int
95
96            -- WWB: Check to make sure that the dependencies are not in the stack
97            -- If they are we don't need to go down this branch
98            SELECT    @Count = COUNT(1)
99            FROM    #Stack    
100            WHERE    #Stack.[Schema] = @RelatedSchema AND 
101                #Stack.[Table] = @RelatedTable
102
103            IF (@Count=0) 
104            BEGIN
105                -- WWB: Recurse
106                EXECUTE SqlAzureRecursiveFind @BaseSchmea, 
107                    @BaseTable, 
108                    @RelatedSchema, @RelatedTable, @Fail OUTPUT
109                IF (LEN(@Fail) > 0)
110                BEGIN
111                    -- WWB: If the Call Fails, Build the Output Up
112                    SET @Fail = @RelatedSchema + '.' + @RelatedTable 
113                        + ' -> ' + @Fail
114                    RETURN
115                END
116            END
117       END
118
119       -- This is executed as long as the previous fetch succeeds.
120    FETCH NEXT FROM table_cursor INTO @RelatedSchema, @RelatedTable;
121    END
122
123    CLOSE table_cursor;
124    DEALLOCATE table_cursor;    
125
126GO    
127
128SET NOCOUNT ON
129
130DECLARE @Schema nvarchar(max)
131DECLARE @Table nvarchar(max)
132DECLARE @Fail nvarchar(max)
133
134-- WWB: Loop Through All the Tables In the Database Checking Each One
135DECLARE list_cursor CURSOR FOR
136      SELECT [Schema], [Table]
137      FROM #TableList
138
139OPEN list_cursor;
140
141-- Perform the first fetch.
142FETCH NEXT FROM list_cursor INTO @Schema, @Table;
143
144-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
145WHILE @@FETCH_STATUS = 0
146BEGIN
147
148    -- WWB: Clear the Stack (Don't you love Global Variables)
149    DELETE #Stack
150
151    -- WWB: Initialize the Input
152    SET @Fail = ''
153
154    -- WWB: Check the Table
155    EXECUTE SqlAzureRecursiveFind @Schema, 
156        @Table, @Schema,
157         @Table, @Fail OUTPUT
158    IF (LEN(@Fail) > 0)
159    BEGIN
160        -- WWB: Failed, Output
161        SET @Fail = @Schema + '.' + @Table + ' -> ' + @Fail
162        PRINT @Fail
163    END
164
165   -- This is executed as long as the previous fetch succeeds.
166    FETCH NEXT FROM list_cursor INTO @Schema, @Table;
167END
168
169-- WWB: Clean Up
170CLOSE list_cursor;
171DEALLOCATE list_cursor;    
172
173DROP TABLE #TableRelationships
174DROP TABLE #Stack
175DROP TABLE #TableList
176DROP PROC SqlAzureRecursiveFind