Functions/Get-SqlDatabase.ps1
|
function Get-SqlDatabase { <# .SYNOPSIS Get list of SQL databases .DESCRIPTION Get list of SQL databases. Can optionally look for a particular database and can optionally include system databases which are excluded by default .PARAMETER ServerInstance Database instance that you wish to connect to. Defaults to $env:COMPUTERNAME .PARAMETER Database String containing text for database name to be LIKE. Wildcards can be used. .PARAMETER IncludeSystemDatabase Switch to include system databases: 'master', 'model', 'msdb', 'tempdb' .EXAMPLE Get-SqlDatabase Returns list of databases that match the criteria .NOTES 1 - User running this function must have Windows authentication to the database server 2 - outputs a psobject containing: Name #> [CmdletBinding()] [outputtype('psobject')] param ( [string] $ServerInstance = $env:COMPUTERNAME, [string] $Database = '*', [switch] $IncludeSystemDatabase ) begin { Write-Verbose -Message "Starting [$($MyInvocation.Mycommand)]" # replace normal wildcards with SQL wildcards. # Need to use .Replace() method as it will do straight character replacement # -replace method uses regular expressions and it will barf on trying to replace '?' as that is special character in regex $Database = $Database.Replace('*', '%') $Database = $Database.Replace('?', '_') $Query = "SELECT Name FROM sys.databases WHERE state = 0 AND Name LIKE '$Database'" if (-not $IncludeSystemDatabase) { $Query += " AND Name NOT IN ('master', 'model', 'msdb', 'tempdb')" } $Query += ' ORDER BY Name' } process { try { $SqlDbParam = @{ ServerInstance = $ServerInstance Database = 'master' Query = $Query } $DatabaseList = Invoke-Sqlcmd @SqlDbParam if (-not $DatabaseList) { Write-Error "No databases that match [$Database]" return } else { Write-Output $DatabaseList } } catch { Write-Error "Could not make SQL connection to [$ServerInstance], either server not up, or SQL not running, or no permissions to connect." return } } end { Write-Verbose -Message "Ending [$($MyInvocation.Mycommand)]" } } |