Get a list of Objects owned by a Schema in Sql Server via Powershell

Scott Newman Powershell, SQL Server February 13, 2013February 13, 2013 1 Minute

Quick script to get a list of objects owned by a schema in sql server. This uses sqlpsx. This doesn’t return the objects themselves, just a list of them.

Import-Module SqlServer
$outputs = @();
$srvName = 'ServerName'
$dbName = 'DatabaseName'
$schemaName = 'SchemaName'

#do not change, used for string parsing below
$startIndex = "@Name='"
$endIndex = "' and @Schema"

$srv = Get-SqlServer -sqlserver $srvName
$db = Get-SqlDatabase -sqlserver $srv -dbname $dbName
$schema = $db.schemas | where{$_.Name -eq $schemaName}
foreach($ownedObj in $schema.EnumOwnedObjects().GetEnumerator())
{
	$arObjs = $ownedObj.Value.Split("/")
	$newName = $arObjs[2].Substring($arObjs[2].IndexOf($startIndex)+ $startIndex.Length)
	
	$output = New-Object -TypeName PSObject -Property @{
		ObjectName = $newName.substring(0, $newName.indexof("'"));
		ObjectType = $ownedObj.Type
	}
	$outputs += $output

}

$outputs | Format-Table -AutoSize