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
Advertisements

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s

%d bloggers like this: