List Databases (and Properties) on SQL Server with PowerShell

Another post in the PowerShell Box of Tricks series. Here is another script which I use to save me time and effort during my daily workload enabling me to spend more time on more important (to me) things!

Todays question which I often get asked is What databases are on that server?

This is often a follow up to a question that requires the Find-Database script. It is often asked by support teams investigating issues. It can also be asked by developers checking the impact of other services on their DEV/UAT environments, by change managers investigating impact of changes, by service managers investigating the impact of downtime, when capacity planning for a new service and numerous other situations.

A simple quick and easy question made simpler with this function which can also be called when creating documentation


Simply call it with Show-DatabasesOnServer SERVERNAME and use the results as you need


This only shows you the name but if you need more information about your databases then have a look and see what you require.

Use Get-Member to see what is there. I ran the following code to count the number of Properties available for Databases (Using PowerShell V3 on SQL Server 2012 SP1 11.0.3350.0 )


154 Properties that you can examine and that is just for databases:-)

Picking out a few properties you could do something like this


If you want aliases for your column headings you will need to add a bit of code to the select.

For Example, maybe you want to Database Name as a heading and the Size in Gb (Its in Mb in the example above) You would need to create a hash table with a Label element and an Expression element. The Label is the column heading and the Expression can just be the data or a calculation on data.

So select Name becomes

The Column Heading is Database Name and the data is the Name property

and select Size becomes

The Column Heading is Size GB and the data is the Size property divided by 1024 to 3 decimal places

then your code would look like this

and the results


and here is the full code

4 thoughts on “List Databases (and Properties) on SQL Server with PowerShell

  1. I’ve tried the first piece of code and gettng this error message:
    The following exception occurred while trying to enumerate the collection: “Failed to connect to server MSSQLSERVER.”.
    At C:\Temp\sqlBackup2.ps1:10 char:1
    + $srv.databases | select name
    + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo : NotSpecified: (:) [], ExtendedTypeSystemException
    + FullyQualifiedErrorId : ExceptionInGetEnumerator

    Could you please help ?

    • Hi, I would recommend that you use dbatools for this.

      You will need to Install It with

      Install-Module dbatools -scope CurrentUser

      and then you can run

      Get-DbaDatabase -SqlInstance NAMEOFINSTANCE

    • I dont think you have set up your SMO object $srv correctly here, if you want to try and make that work but see previous comment about dbatools

Please feel free to comment on this post. All comments are moderated first before appearing on the site

This site uses Akismet to reduce spam. Learn how your comment data is processed.