List all contents of path in PowerShell

And now for something completely different… This little PowerShell script might be useful when trying to locate the biggest files on your hard drive (I’m always low on disk space).

Get-ChildItem d:\ -recurse | where {$_.Length -ge 0} | sort -Property Length -desc | select Length, Directory, Name, LastAccessTime, Mode | ogv 

It takes all contents of D:\ (Get-ChildItem d:\ -recurse), looks for files of specific size ($_.Length -ge 0), orders them by size descending (sort -Property Length -desc) and outputs interesting information to the Out-GridView. If you want to see it in Excel instead, replace ogv with Export-Csv and then import it to Excel (or use method described here).

Enumerating SQL Server instances in a network with Power Shell

Ever wondered how many SQL Server instances are there in your network? Or maybe you wanted to check a settings or run a script on all of them? Of course you can use Registered Servers functionality and run a query on all servers in a group, but this can save you the pain of adding all servers one by one.

Since Power Shell builds on .NET framework, you are free to use all the mechanisms available in it. The one we are interested in here is SqlDataSourceEnumerator, which allows you to enumerate all available instances of SQL Server in a local network. The only prerequisite is that servers must have SQL Server Browser service running and the task is completed by the following one-liner:


The output you get is a table consisting of four columns – server and instance name, version and information if the server is clustered.

ServerName                                 InstanceName                               IsClustered                                Version                                 
----------                                 ------------                               -----------                                -------                                   
PHOBOS-MOBILNY                                                                        No                                         9.00.4035.00                              

Now take a simple extension of it and you are able to connect to every single SQL Server in your network. It will give you the list in standard SERVER\INSTANCE format. Then you can expand it further using SMO or Power Shell snap-in and you will get all the information you need from all servers at the same time.

[System.Data.Sql.SqlDataSourceEnumerator]::Instance.GetDataSources()  | % {if ($_.InstanceName -eq "".ToString) { $_.ServerName + "\" + $_.InstanceName} else { $_.ServerName }}

I’m awaiting – as always – your feedback, what’s good and what still needs to be improved. Or maybe there’s just something you would like to share.

Hello, world! :) and off we go – with Power Shell for DBA

First, let me express I am really happy to post here. I was following BeyondRelational for quite some time and learned a lot (especially from T-SQL Challenges, I guess) and I have finally decided to contribute a bit. I hope I am able to deliver some quality, so any comments are welcome.

So, let’s start with something not really obvious. Everyone knows about Power Shell, but who uses it for database administration? Of course since SQL Server 2008 there is an option of running Power Shell from within SSMS, but there are situations when it comes really handy, for example, when running queries against multiple servers and/or databases.

The idea is taken from one of the blogs I’ve come across once. It states that Power Shell exposes useful methods for database developers, so let’s put it to good use for administrative work.
In this scenario you get a list of servers from somewhere (it may be a text file or Active Directory), connect to each of them using SMO and get the information you want. For testing purposes, let’s grab name, compatibility level, recovery model, size and some other options. For details or extensions, MSDN website is a good place to start.

Let’s break it to bits. First we get the servers we want to query, then we’ll work on getting the information.

The simplest way to get the list of servers to be queried is to place them in a file, then read it. You can fetch with one line:

$serverList = Get-Content 'filename'

The downside is that, if your server list changes, you have to remember to update your configuration file and since you are probably in a domain, this information is already out there somewhere just waiting for you to reach it. If you have a domain group containing all the servers, this gets really easy. Just remember to either run Active Directory Module for Power Shell, or include Import-Module ActiveDirectory before this:

$serverList = Get-ADComputer –Filter {MemberOf –eq "group_name"} | select Name

Normally, the Get-ADComputer returns more information than we really need, so first we get the data and then just take what’s necessary – in this case, Name. It’s, however, possible that the situation differs and you don’t have a group, you have a separate OU instead. In this case, you have to prepare an LDAP query to get to proper path and provide a dummy filter (Get-ADComputer requires Filter parameter):

$serverList = Get-ADComputer –Filter {Name –like "*"} –LDAPFilter “ou=Servers,ou=Computers,dc=contoso,dc=com” | select Name

Since we have our list of servers, we can start querying them. We have to load Microsoft.SqlServer.SMO (Server Management Objects) namespace first. Note that SMO does not support SQL Server version below 2000, but they should be pretty rare at the moment.

After we have the namespace ready, we will loop through all the servers. For each of the servers, we’ll create an instance of Microsoft.SqlServer.Management.Smo.Server class, and then we’ll look into Databases collection. To keep the output nicely formatted, we’ll store the results in temporary variable $out, which is displayed using Out-GridView at the end.

$out = @()
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null
foreach ($server in $serverList)
	$s = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $server
	$dbs | select Name, CompatibilityLevel, RecoveryModel, AutoClose, AutoShrink, Owner, Size, LogFiles | Add-Member -Name "Server" -Value $server -MemberType NoteProperty -PassThru -Force -OutVariable +out | Out-Null
$out | ogv -Title "Database information"

There are more options starting from here, for instance you can list all permissions in all databases (again, refer to MSDN for detailed description):

$out =@()
foreach ($db in $dbs)
	$db.EnumObjectPermissions() | where {$_.ObjectSchema -ne "sys" -and $_.ObjectSchema -ne "INFORMATION_SCHEMA"} | select @{Name="dbName";Expression={$db.Name}}, * -OutVariable +out | out-null
$out | ogv -Title "Permissions on all objects in all databases"

I hope you find it interesting how you can make a bridge between development and administration – it’s a way to administrative stuff programatically. Anyway, I hope you enjoy my first post here and again – feel free to comment.