Find in Which Content Database Site Collection Lives

Requirement: Get which site collections resides in which content database.

Solution: Simplest method is: Navigate to Central Admin >> Application Management >> View All Site Collections .  This will display the content database name of a particular site collection.

Site collection's content database in SharePoint 2010

PowerShell Script to get site collection's content database:
This code outputs the content database name of given site collection.

$site=Get-SPSite "http://sharepoint.crescent.com
write-host $site.ContentDatabase.Name

One-Liner to Find Content Databases of Site collections in SharePoint 2013:

Get-SPWebApplication | Get-SPSite | Select URL, @{Name="Database"; Expression={$_.ContentDatabase.Name}} 

Using STSADM to get Content Database of All site collections: Execute the STSADM as

This will generate the SitesList.xml file with following information:

1.  URL of site collections
2.  Primary Owner
3.  Secondary Owner
4.  Content Database
5.  Storage Used
6.  Storage Warning
7.  Storage Max

Report for site collection content database in SharePoint:
On a regular basis, Had to generate report with site collection databases information. PowerShell comes to rescue again!

function SitesInDB([string]$url)
{

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SharePoint") > $null
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SharePoint.Administration") > $null

$rootSite = new-object Microsoft.SharePoint.SPSite($url)
$webApplication = $rootSite.WebApplication

$ContentDBCollection = $WebApplication.ContentDatabases

$DBName = [Microsoft.SharePoint.Administration.SPContentDatabase].GetProperty("Name")


#If Data directory already exists remove it
if (Test-Path "D:\Reports\SitesInDB\Data")
{
    Remove-Item "D:\Reports\SitesInDB\Data" -Recurse
}

# create a folder
mkdir D:\Reports\SitesInDB\Data

# Traverse through all the content databases and run the stsadm command
foreach($ContentDB in $ContentDBCollection){  
 $CurrentDBName = $DBName.GetValue($ContentDB, $null)
 stsadm -o enumsites -url $url -databasename $CurrentDBName > D:\Reports\SitesInDB\Data\$CurrentDBName.txt
 }

# check the folder "Data-YEAR-MM-DD exists already if so delete it
$newName = get-date -uformat "%Y-%m-%d"
if (Test-Path "D:\Reports\SitesInDB\Data-$newName")
{
    Remove-Item "D:\Reports\SitesInDB\Data-$newName" -Recurse
}

#rename the folder "Data" to "Data-YEAR-MM-DD"
ren D:\Reports\SitesInDB\Data D:\Reports\SitesInDB\Data-$newName
}

#Call the function
sitesInDB 'http://SharePoint.crescent.com'