This is a post for TSQL Tuesday #77 about my favorite SQL Server Feature.  I write a lot of automations using PowerShell and I use SMO (Shared Management Objects) all the time. This is my favorite feature because it allows me to get information from SQL Server using an object model instead of using TSQL.  I am pretty good at TSQL and have a lot of SQL scripts that I use, but when I am gathering information or just need a quick piece of information, SMO can give it to me with a canned script that I call with parameters and there is no real client that I have to have installed on the server or my workstation.  It is really lightweight and I can have the information back in an object form.

SQL Server has a lot to offer, but one maybe little known option for all you SQL Server people, is that Microsoft releases Feature Packs for each version of SQL Server/Service Pack so that you can have the objects without having to use the Management Studio and other tools.  To get started you will need the latest version so that you can connect backwards to lower versions. You get these objects for 2014 here.

Then you will need the following to get started in the order below: (either x86 or x64 for your platform for each item)

• SqlClrTypes
• Shared Management Objects (SMO)
• PowerShell Tools

The SqlClrTypes must come first because it has objects for the types that are CLR, like HierarchyID, and geography types, etc. The Shared Management Objects contain the libraries that you use to access SQL objects via a .NET object. Then there is the PowerShell Tools which contains the SQL Server provider that allows you to import a module in PowerShell and have access to SQL Server in a path like structure.

Once you have these installed, you can then use PowerShell, or VB or C# to connect to SQL Server and use them. I will give you an example below to show you how they work and you can take it from there.

Add-Type -AssemblyName "Microsoft.SqlServer.Smo, Version=12.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"
$server = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server -Args localhost  # replace this with your server name

$server.Databases | Format-Table * -AutoSize

This is just a quick example of how to get information from SQL Server using SMO.  The command above basically is generic and not always the most useful, but it illustrates the point that the objects have properties that you are used to seeing in Management Studio like the Name of the database and Recovery Model. These properties are not always easy to get to in the tools, but in PowerShell and SMO they are.

If you want more in depth information on how to get started with SQL PowerShell, take a look at my Stairway on SQL PowerShell on www.sqlservercentral.com and many other Stairways.

Happy TSQL Tuesday #77.