SMS SQL for Collections with Duplicate Names
/*
The following SQL will display any collections which appear to have a duplicate name. In some cases this may be normal, however this is a good way to find problems and inconsistencies.
*/
SELECT col.[CollectionID]
,[Name]
,[parentCollectionID]
,[Comment]
,[OwnedByThisSite]
,[ReplicateToSubSites]
,[LastChangeTime]
,[LastRefreshTime]
,[RefreshType]
,[CurrentStatus]
,[MemberClassName]
,[LastMemberChangeTime]
FROM v_Collection col
left outer join
v_CollectToSubCollect sub
on col.collectionid = sub.subCollectionid
Where Name in
(
SELECT Name
FROM v_Collection
Group By Name
Having Count(*) > 1
)
order by name, collectionid
The following SQL will display any collections which appear to have a duplicate name. In some cases this may be normal, however this is a good way to find problems and inconsistencies.
*/
SELECT col.[CollectionID]
,[Name]
,[parentCollectionID]
,[Comment]
,[OwnedByThisSite]
,[ReplicateToSubSites]
,[LastChangeTime]
,[LastRefreshTime]
,[RefreshType]
,[CurrentStatus]
,[MemberClassName]
,[LastMemberChangeTime]
FROM v_Collection col
left outer join
v_CollectToSubCollect sub
on col.collectionid = sub.subCollectionid
Where Name in
(
SELECT Name
FROM v_Collection
Group By Name
Having Count(*) > 1
)
order by name, collectionid
About the Author
Emmanuel Tsouris is a Systems Management veteran and Developer Advocate specializing in PowerShell and Cloud Automation. He maintains DotVBS to preserve legacy knowledge for the "archaeologist admin."
Ready to move from VBScript to the Cloud? Check out his book, Pro PowerShell for Amazon Web Services.
Visit EmmanuelTsouris.com for his latest projects.
As an Amazon Associate, I earn from qualifying purchases at no cost to you.
Comments