Finding all collections that are ultimately limited by a collection

We sometimes have to update a particular collection, but how many collections are ultimately limited by that one?  For instance, for “All Systems”:

declare @LimitingCollection nvarchar (8) = 'SMS00001';
WITH Recursive_CTE AS
(
SELECT
 child.SiteID,
CAST(child.SiteID as varchar(max)) SiteID,
CAST('>> ' as varchar(max)) LVL,
CAST(child.SiteID as varchar(max)) Hierarchy,
 1 AS RecursionLevel
FROM v_Collections child
WHERE SiteID = @LimitingCollection
UNION ALL
SELECT
 child.SiteID,
CAST(LVL + child.SiteID as varchar(max)) AS SiteID,
CAST('>> ' + LVL as varchar(max)) AS LVL,
CAST(Hierarchy + ':' + CAST(child.SiteID as varchar(max)) as varchar(max)) Hierarchy,
 RecursionLevel + 1 AS RecursionLevel
FROM Recursive_CTE parent
INNER JOIN v_Collections child ON child.LimitToCollectionID = parent.SiteID
)
SELECT SiteID, Hierarchy, RecursionLevel FROM Recursive_CTE ORDER BY Hierarchy

Change the parameter to give the results for a sub-limiter:

declare @LimitingCollection nvarchar (8) = ‘XXX0067F’;

Results look like:

SiteID   Hierarchy                             RecursionLevel
-------- ------------------------------------- --------------
XXX0067F XXX0067F                              1
XXX00680 XXX0067F:XXX00680                     2
XXX00681 XXX0067F:XXX00681                     2
XXX00682 XXX0067F:XXX00681:XXX00682            3
XXX009A8 XXX0067F:XXX00681:XXX00682:XXX009A8   4
XXX00C5F XXX0067F:XXX00681:XXX00682:XXX00C5F   4
XXX01020 XXX0067F:XXX00681:XXX00682:XXX01020   4
XXX013E5 XXX0067F:XXX00681:XXX00682:XXX013E5   4
XXX0152A XXX0067F:XXX00681:XXX00682:XXX0152A   4
XXX0156D XXX0067F:XXX00681:XXX00682:XXX0156D   4
XXX01581 XXX0067F:XXX00681:XXX00682:XXX01581   4
XXX01586 XXX0067F:XXX00681:XXX00682:XXX01586   4
XXX015AC XXX0067F:XXX00681:XXX00682:XXX015AC   4
XXX00683 XXX0067F:XXX00681:XXX00683            3
XXX008B8 XXX0067F:XXX00681:XXX008B8            3
XXX008DC XXX0067F:XXX00681:XXX008DC            3
XXX013D5 XXX0067F:XXX00681:XXX008DC:XXX013D5   4
XXX0141C XXX0067F:XXX00681:XXX008DC:XXX0141C   4
XXX00DD1 XXX0067F:XXX00681:XXX00DD1            3
XXX01342 XXX0067F:XXX00681:XXX01342            3
XXX015C6 XXX0067F:XXX00681:XXX015C6            3

(21 rows affected

Thanks to http://www.kodyaz.com/t-sql/sql-server-recursive-query-with-recursive-cte.aspx for the recursive query logic.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s