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.

Get sharepoint site collection from content database using powershell

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 http://Site-Collection-URL

Salaudeen Rajack

Salaudeen Rajack is a SharePoint Architect with Two decades of SharePoint Experience. He loves sharing his knowledge and experiences with the SharePoint community, through his real-world articles!

Leave a Reply