declare @UsedItems Table
( itemid int primary key, contentID int )
insert into @UsedItems
Select ItemID, ContentID from vefn_GetVersionItems('')

declare @DiscItems Table
( itemid int primary key, ContentID int)
insert into @DiscItems
Select ItemID, II.ContentID from Items II
Join Contents CC ON CC.ContentID = II.ContentID
where ItemID not in(select ItemID from @UsedItems)
And CC.Type is not null

select * from @discitems


--select COUNT(*) HowManyTotal from Items II
--Join Contents CC ON CC.ContentID = II.ContentID
--where ItemID in (select ItemID from @DiscItems)

-- Disconnected Groups
select ItemID,II.ContentID, Type, Number, Text, CC.UserID, CC.DTS
, (select COUNT(*) from vefn_SiblingItems(ItemID,null))-1 Siblings
, (select COUNT(*) from vefn_SiblingChildrenItems(ItemID))-1 SiblingChildren
, (select COUNT(*) from transitions 
	where fromid in(select ContentID from vefn_SiblingChildrenItems(ItemID)) 
	and toid in (select ItemID from @UsedItems)) FromDiscTrans
, (select COUNT(*) from transitions 
	where fromid in(select ContentID from @UsedItems) 
	and toid in (select ItemID from vefn_SiblingChildrenItems(ItemID))) ToDiscTrans
, (Select Count(*) from Annotations AA
	where AA.ItemID In (select ItemID from vefn_SiblingChildrenItems(II.ItemID))) Annotations
, (Select Count(*) from ROUsages
	where ContentID In (select ContentID from vefn_SiblingChildrenItems(ItemID))) RefObjects
, (Select Count(*) from Entries
	where ContentID In (select ContentID from vefn_SiblingChildrenItems(ItemID))) LibraryDocs
from Items II
Join Contents CC ON CC.ContentID = II.ContentID
where ItemID in (select ItemID from @DiscItems)
and PreviousID is null
and ItemID not in (Select ItemID from Parts)

select vnf.FolderName FromFolderName, FromID, dbo.ve_GetShortPathFromContentID(fromid)Source, vnt.FolderName ToFolderName, ToID, dbo.ve_GetShortPath(toid) Destination from Transitions TT
left Join vefn_GetVersionItems('') VIF on VIF.ContentID = TT.FromID left JOIN vefn_GetVersionNames() VNF on VNF.versionID = VIF.VersionID
left Join vefn_GetVersionItems('') VIT on VIT.Itemid =TT.ToID left JOIN vefn_GetVersionNames() VNT on VNT.versionID = VIT.VersionID
where ToiD in(select itemID from @DiscItems) and FromiD in(select contentID from @UsedItems)

select vnf.foldername FromFolderName, FromID, dbo.ve_GetShortPathFromContentID(fromid)Source, vnt.foldername ToFolderName, ToID, dbo.ve_GetShortPath(toid) Destination from Transitions TT
left Join vefn_GetVersionItems('') VIF on VIF.ContentID = TT.FromID left JOIN vefn_GetVersionNames() VNF on VNF.versionID = VIF.VersionID
left Join vefn_GetVersionItems('') VIT on VIT.Itemid =TT.ToID left JOIN vefn_GetVersionNames() VNT on VNT.versionID = VIT.VersionID
where ToiD in(select itemID from @UsedItems) and FromiD in(select contentID from @DiscItems)

select vnf.FolderName, AA.ItemID, dbo.ve_GetShortPath(AA.ItemID)Location , AA.SearchText from Annotations AA
left Join vefn_GetVersionItems('') VIF on VIF.ItemID = AA.ItemID left JOIN vefn_GetVersionNames() VNF on VNF.versionID = VIF.VersionID
where AA.ItemiD in(select itemID from @DiscItems)

select vnf.FolderName, RO.ContentID, dbo.ve_GetShortPathFromContentID(RO.ContentID)Location , RO.ROID from ROUsages RO
left Join vefn_GetVersionItems('') VIF on VIF.ContentID = RO.ContentID left JOIN vefn_GetVersionNames() VNF on VNF.versionID = VIF.VersionID
where RO.ContentID in(select ContentID from @DiscItems)

select vnf.FolderName, EE.ContentID, dbo.ve_GetShortPathFromContentID(EE.ContentID)Location , Doc.LibTitle from Entries EE Join Documents DOC ON EE.DocID=DOC.DocID
left Join vefn_GetVersionItems('') VIF on VIF.ContentID = EE.ContentID left JOIN vefn_GetVersionNames() VNF on VNF.versionID = VIF.VersionID
where EE.ContentID in(select ContentID from @DiscItems)