Get-SQLDatabase Using Powershell With Examples

Another method to query a remote SQL instance and collect DB details is the get-sqldatabase commandlet. Using the “-ServerInstance” parameter you can easily summarize the databases on that instance. This commandlet may require you to import a new module if you have not already.

To just get a list of the databases and their properties from a remote SQL instance you can use the following:

import-module sqlserver   ## may need to import

get-sqldatabase -ServerInstance MyServer01\TestSQL01

Here at DB Tales we tend to use the Invoke-SQLCmd commandlet as you can easily use one of your existing scripts to return specialized data. This post is about the Get-SQLDatabases commandlet so I add this here just for reference.

invoke-sqlcmd -ServerInstance "MyServer01\TestSQL01" -Query "SELECT * FROM sysdatabases"

Note: if you get “get-sqldatabase not recognized”, you probably do not have the powershell module imported. Use “import-module sqlserver”

Get-SQLDatabase With Variable Example

Assigning the results to a variable offers some more options. We can get a list of databases and some of their details quickly as well as adding it to a foreach and digging deeper. Here’s a few basic queries for the DBA.

$databases = get-sqldatabase -ServerInstance "MyServer01\TestSQL01" 

$databases | format-table

$databases | Select-Object Name, Status, Size, Owner

$databases.Count   ## number of DB's

$databases.Name    ## Just a Database list

Get One Database

We can also specify only one database that we want to collect information on.

get-sqldatabase -Name "SomeDBName" -ServerInstance "MyServer01\TestSQL01" 

$mydb.PrimaryFilePath  ## only returns first file

$mydb.Users      ## list database users

$mydb.SpaceAvailable   ## Available space in KB

$mydb.Size       ## size summary like sp_helpdb

$mydb.DataSpaceUsage   ## actual data used in data files

$mydb.IndexSpaceUsage  ## actual data used in index files

Get Table Data

Pull some quick details about the tables in this single database.

$mydb.Tables

$mydb.Tables | Select-Object Schema, Name

($mydb.Tables).Name

($mydb.Tables).Count

Using a Foreach On Each Database

$databases = get-sqldatabase -ServerInstance ESQLENT18\SQLP918

foreach($db in $databases) {
    $systemdb = @('master','model','msdb','tempdb')

    $db | Select-Object Name, Status, Size | Where-Object {$_.Name -notin $systemdb }
}