I previously posted an article explaining how to audit your SQL Servers with Powershell. In this article I wrote SMO properties to csv files. As there are a large number of properties you may be unsure of which ones you may need in the future. Luckily Powershell doesn’t make us fetch every single property manually; we can pipe the contents of an object to the Export-Csv cmdlet.

# Set sql server name
$sqlserver = "localhost\sql2005";
# Load SMO extension
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | out-null;
# Create an SMO Server object
$srv = New-Object "Microsoft.SqlServer.Management.Smo.Server" $sqlserver;
# Get DatabaseCOllection from the sql server instance
$databases = $srv.Databases;
# Write all these properties to a csv file
$databases | Export-Csv "$env:USERPROFILE\sqlserverdatabases.csv";

This will dump all the properties of Microsoft.SqlServer.Management.Smo.Database into sqlserverdatabases.csv in the user directory. Some of the properties are collections themselves so you get the Type in the csv sheet rather then the values. But nevertheless a large amount of useful information is provided. I’ve uploaded a sample file.

I’m itching to build a CMDB using Powershell to provide the data in csv files, SSIS to import it, and SSRS to provide some cool dashboard reports. Before the advent of Powershell this task would have been so much more difficult. I’ll certainly be developing my skills in this area as I’m betting on it becoming so important.