A duplicate field name "xxxx" was found

A sub site needs to be changed to a separate site collection. When trying to create the root site of the site collection, I got the error message below:

A duplicate field name "Change_x0020_Category" was found

In the "Site columns gallery" (through the "site settings" of the root site, there is only one "Change Category" site column. But when checking it through the freeware "SharePoint Manager 2010", there do have two "Change Category" fields with different field type.

I easily figured out that one of the column was obsoleted long time ago. However, I cannot delete it. Obviously it is used by some site content types or lists.

So, I changed the field title through "SharePoint Manager 2010" so we can tell the difference, then wrote some PowerShell script to check it.


function ListSiteColumnUsage([string]$SiteUrl, [string]$SiteColumnName)
{
write-host "SiteUrl:" $SiteUrl ", SiteColumnName:" $SiteColumnName

$web = Get-SPWeb $SiteUrl 
$site = $web.Site
#$column = $site.RootWeb.AvailableFields[$SiteColumnName] 
$column = $web.AvailableFields[$SiteColumnName] 
write-host "Title: " $column.Title -nonewline
write-host ", Type: " $column.Type -nonewline
write-host ", StaticName: " $column.StaticName -nonewline
write-host ", Id: " $column.Id

write-host "List Usages" -foregroundcolor Red
$column.ListsFieldUsedIn() | ForEach-Object { 
$SubWeb = $site.AllWebs[$_.WebID] 
write-host "SubWeb.ServerRelativeUrl:" $SubWeb.ServerRelativeUrl
Write-Host "SubWeb.Title:" $SubWeb.Title
$list = $SubWeb.Lists[$_.ListID] 

Write-Host "ListUrl:" $list.RootFolder.Url ", ColumnName:" $column.Title ", StaticName:" $column.StaticName ", ColumnID:" $column.ID ", Type:" $column.Type 
} 

write-host "Content Type Usages" -foregroundcolor Red
$cts = $site.rootweb.ContentTypes
ForEach ($ct in $cts)
{
 ForEach ($field in $ct.Fields)
 {
if ($field.Id -eq $column.ID)
{
Write-Host "Content Type Name:" $ct.Name
}
 }
}

$site.Dispose()
}

ListSiteColumnUsage "http://spserver/sites/site1" "Change Category"


However, to delete the site column, we may need more PowerShell script.



function DeleteSiteColumn([string]$SiteUrl, [string]$SiteColumnName)
{
write-host "SiteUrl:" $SiteUrl ", SiteColumnName:" $SiteColumnName

$web = Get-SPWeb $SiteUrl 
#$column = $site.RootWeb.AvailableFields[$SiteColumnName] 
$column = $web.Fields[$SiteColumnName] 
write-host "Title: " $column.Title -nonewline
write-host ", Type: " $column.Type -nonewline
write-host ", StaticName: " $column.StaticName -nonewline
write-host ", Id: " $column.Id

write-host "Content Type Usages"
$cts = $web.ContentTypes
ForEach ($ct in $cts)
{
 ForEach ($field in $ct.Fields)
 {
if ($field.Id -eq $column.ID)
{
Write-Host "Content Type Name:" $ct.Name -foregroundcolor Red
}
 }
}

$web.Fields.Delete($column)

$site.Dispose()
}

DeleteSiteColumn "http://spserver/sites/site1" "Change Category"



I think we can use these scripts to delete any corrupted site columns.
After removing the obsoleted site columns, I got another error at the same stage:

A duplicate content type name "Policy Document" was found.

Yes, there are duplicated content types, which also can only be seen from "SharePoint Manager 2010". As per this article, the problem is caused by "Content Type Hub".  Again, I need to delete the invalid content type. Again, it is used by some lists.

I wrote some code to get the list names:


function ListContentTypeUsage([string]$SiteUrl, [string]$CTName)
{
write-host "SiteUrl:" $SiteUrl ", Content Type Name:" $CTName
$site = Get-SPSite $SiteUrl 

$ct = $site.rootweb.ContentTypes[$CTName]
Write-Host "Content Type Name:" $ct.Name

foreach($web in $site.AllWebs) 
{ 
ForEach ($list in $web.Lists)
{
 ForEach ($listct in $list.ContentTypes)
 {
if ($listct.Id -eq $ct.ID)
{
Write-Host "Content Type Name:" $listct.Name
}
 }
}
}

$site.Dispose()
}

ListContentTypeUsage "http://spserver/sites/site1" "XXXX Document"


Then I removed those content types from the lists, and then deleted those content types.

Done!