function Get-DatabaseSize{
<#

.SYNOPSIS
Scripts out database size properties

.DESCRIPTION
This module will script sql server database sizes for all databases on a given server instance

.PARAMETER serverInstance
The name of the sql server instance that the jobs reside on

.PARAMETER IncludeSystemDBs
Switch to include system databases in results 

.EXAMPLE
Get database sizes for all databases including system databases
Get-DatabaseSize -serverInstance 'InstanceName' -IncludeSystemDBs

.EXAMPLE 
Get database sizes for all databases via pipeline
@('instance1', 'instance2', 'instance3') | Get-DatabaseSize -IncludeSystemDBs

.NOTES
This assumes that you have access to the sql server via windows-authentication. 

#>

	param(
		[Parameter(Mandatory=$true,Position=0,ValueFromPipeline=$true)]
		[string]$serverInstance,
		[Parameter(Mandatory=$false,Position=1,ValueFromPipeline=$false)]
		[switch]$IncludeSystemDBs
	)
	begin{
		$objects = @();
		$srvConn = New-Object microsoft.SqlServer.Management.Common.ServerConnection
		$srvConn.LoginSecure = $true;
		$srvConn.ServerInstance = $serverInstance
		$server = New-Object microsoft.SqlServer.Management.Smo.Server $srvConn
	}
	process{
		foreach($db in $server.Databases | where{$_.IsSystemObject -eq $IncludeSystemDBs -or -not $_.IsSystemObject}){
			$logSize = 0;
			$UsedLogSpace = 0;
			foreach($log in $db.LogFiles){
				$logSize += $log.Size
				$UsedLogSpace += $log.UsedSpace
			}
			$obj = New-Object -TypeName PSObject -Property @{
				ServerInstance = $serverInstance
				DatabaseName = $db.Name
				DatabaseSizeMB = $db.Size
				DataSpaceUsageKB = $db.DataSpaceUsage
				IndexSpaceUsageKB = $db.IndexSpaceUsage
				UsedLogSpaceKB = $UsedLogSpace
				LogSizeKB = $logSize
			}
			$objects += $obj;
		}
		$objects | SELECT ServerInstance, DatabaseName, DatabaseSizeMB, DataSpaceUsageKB, IndexSpaceUsageKB, UsedLogSpaceKB, LogSizeKB 
	}
	end{
		$srvConn.Disconnect();
	}
}

function GetRoleMembers{
	[CmdletBinding()]
	param(
		[Parameter(Mandatory=$True, ValueFromPipeline=$True)]
		[string[]]$serverInstance,
		[Parameter(Mandatory=$false, ValueFromPipeline=$false)]
		[string]$userName = $null,
		[Parameter(Mandatory=$false, ValueFromPipeline=$false)]
		[string]$password = $null
	)
	BEGIN{
		[void][reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo")
		[void][reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoEnum")
		[void][reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")
		$outputs = @();
	}
	PROCESS{
		
		$srvConn = New-Object "Microsoft.SqlServer.Management.Common.ServerConnection"
		$srvConn.ServerInstance = $serverInstance
		$srv = New-Object Microsoft.SqlServer.Management.Smo.Server $srvConn
		if($userName.Length -ne 0 -and $password.Length -ne 0){
			$srvConn.LoginSecure = $false;
			$srvConn.Login = $userName
			$srvConn.Password = $password
		}
		else{
			$srvConn.LoginSecure = $true;
		}
		
		#$s = $srv.EnumMembers([Microsoft.SqlServer.Management.Smo.RoleTypes]::All);

		foreach($role in $srv.Roles){
			#[string[]] $roleMembers = 
			$output = New-Object -TypeName PSObject -Property @{
				ServerName = $serverInstance
				Database = '[Server Role]'
				Role = $role.Name
				Members = $role.EnumServerRoleMembers() #-join ', '
			}
			$outputs += $output
		}

		foreach($database in $srv.Databases){
			foreach($role in $database.Roles){
				$output = New-Object -TypeName PSObject -Property @{
					ServerName = $serverInstance
					Database = $database.Name
					Role = $role.Name
					Members = $role.EnumMembers()# -join ', '
				}
				$outputs += $output
			}
		}

	}
	END{
		$outputs | where{$_.Members -ne ''} | SELECT ServerName, Database, Role, Members 
	}
}