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




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




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 for the recursive query logic.


Leave a Reply

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

You are commenting using your 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 )

Google+ photo

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

Connecting to %s