Finding the limiting collection chain for a collection

declare @SiteID varchar (8) = ‘XXX01586’

declare @LimitToCollectionID varchar (8) = (select LimitToCollectionID from vCollections where SiteID = @SiteID)

declare @LinkedList nvarchar (max) = (select SiteID + ‘ – ‘ + CollectionName + CHAR(13) from vCollections where SiteID = @SiteID)

while (1 = 1)

begin

select @SiteID = (select LimitToCollectionID from vCollections where SiteID = @SiteID)

if @SiteID is null

break

set @LinkedList = @LinkedList + (select SiteID + ‘ – ‘ + CollectionName + CHAR(13) from vCollections where SiteID = @SiteID)

end

select @LinkedList

——————————————————————————————-
XXX01586 – ITS OUs in the XXX.ABCDEF.NET Domain

XXX00682 – XXX Workstations with Client

XXX00681 – XXX Workstations

XXX0067F – XXX Devices

XXX0078D – All Systems without Sensitive Data Machines

SMS00001 – All Systems

(1 row(s) affected)

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 )

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