SQL SMO PowerShell

Posted: May 12, 2013 | Author: codewarren | Filed under: Uncategorized |Leave a comment

Some simple PS that has potential.

	Just some shortcuts on top of SMO to add powershell flavor.
	Basically you pick from a collection, and choose an item, rinse and repeat.
	Easier done than said.

	Note: Be careful with this, you are on top of SMO and that can kill things.
	Note: Piping any SMO object at all to Export-Clixml looks interesting.

	TODO: Needs more documentation.
	TODO: Add script options.
	TODO: Add depends function with the dependency walker stuff.
	TODO: Consolidate pick and choose function, make it smarter.

	e.g.
	(express).Databases \| pick Northwind \| choose Tables \| list
	(express).Databases \| pick Northwind \| choose Tables \| pick Suppliers \| script
	(express).Databases \| pick Northwind \| choose Tables \| pick Suppliers \| Select-Object -Property Columns
	(express).Databases \| pick Northwind \| choose Views \| owner dbo \| list

view raw

SQL SMO PowerShell

hosted with ❤ by GitHub

	[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") \| Out-Null;
	function express() {
	server ".\sqlexpress"
	}
	function server($name) {
	New-Object Microsoft.SqlServer.Management.Smo.Server $name
	}
	function list() {
	$input \| Select-Object -Property Name
	}
	function pick($name) {
	$input \| Where-Object {$_.Name -eq $name}
	}
	function choose($name) {
	$input \| Select-Object -ExpandProperty $name
	}
	function owner($owner) {
	$input \| Where-Object {$_.Owner -eq $owner}
	}
	function script() {
	$input \| ForEach-Object {$_.Script()}
	}

view raw

sql.smo.ps1

hosted with ❤ by GitHub

codewarren