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

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

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.