PoshFunctions

2.2.9

Functions/Get-SqlDatabase.ps1

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
 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
#>

    # todo Change += to System.Collections.Arraylist

    [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 -Message "No databases that match [$Database]"
                break
            } else {
                Write-Output -InputObject $DatabaseList
            }
        } catch {
            Write-Error -Message "Could not make SQL connection to [$ServerInstance], either server not up, or SQL not running, or no permissions to connect."
            break
        }
    }

    end {
        Write-Verbose -Message "Ending [$($MyInvocation.Mycommand)]"
    }
}