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

image

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

image

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 )

image

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

Picking out a few properties you could do something like this

image

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

select @{label=”Database Name”;Expression={$_.Name}}

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

and select Size becomes

Select @{label=”Size GB”;Expression={“{0:N3}” -f ($_.Size/1024)}}

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

$srv.databases|select @{label=”Server”;Expression={$_.Parent.name}},`
@{label=”Database Name”;Expression={$_.Name}}, Owner, Collation, CompatibilityLevel,`
RecoveryModel, @{label=”Size GB”;Expression={“{0:N3}” -f ($_.Size/1024)}}|`
Format-Table -Wrap –AutoSize

and the results

image

You can get the code for the function here Show-DatabasesOnServer

Advertisements

One thought on “List Databases (and Properties) on SQL Server with PowerShell

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s