Get All SharePoint Site Collections from a Content Database using PowerShell
During audit/migrations, we had a requirement to generate reports on SharePoint content databases and list of sites collections lives in each content database.
PowerShell script to Get all content databases and their Site collections:
Add-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue
#Get all SharePoint content databases available
$ContentDatabases = Get-SPContentDatabase
#Loop through each content database
foreach($ContentDb in $ContentDatabases)
{
Write-Host "`nContent Database Name: $($ContentDb.Name) Size:$($ContentDb.DiskSizeRequired/1MB) MB " -ForegroundColor DarkGreen
#Get all site collections in the content database
Write-Host "Site Collections List:" -ForegroundColor Blue
foreach($site in $ContentDb.Sites)
{
write-host $site.url
}
}
The One liner for the above script would be:
Get-SPContentDatabase | ForEach-Object { Write-Output "* $($_.Name)”; foreach($Site in $_.Sites){write-Output `t$Site.url}}
This PowerShell script outputs all SharePoint content databases with their size, and for each of the content database, it gives the list of site collections within the content database.
Get All Site Collections in a Specific Content Database:
Let’s change the script slightly to get all site collections lives in a particular content database:
Add-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue
#Set the Content Database Name
$ContentDbName = "SP2010_SharePoint_ContentDB"
#Get the Content Database
$ContentDb = Get-SPContentDatabase -Identity $ContentDbName
#Iterate through each site collection in the Content database
foreach($site in $ContentDb.Sites)
{
write-host $site.url
}
How to Find in Which Content Database a particular Site collection Lives?
Simple! Just use: Get-SPContentDatabase -site https://Site-Collection-URL
Simple! Just use: Get-SPContentDatabase -site https://Site-Collection-URL