Most people in the SQL Server world are at least passingly familiar with SMO – SQL Server Management Objects. It’s essentially just an object-oriented structure for SQL Server objects like Instances, Databases, etc.
Using Powershell, it’s incredibly easy to do a wide variety of tasks using the SMO framework. There’s a pretty intuitive hierarchy to the whole thing, and if you’re at all comfortable in dealing with arrays and collections in PS it’s not too hard to do something like:
– Get a list of tables in a database
– Get all the nonclustered indexes in each table
– Check the fragmentation of every index in each table
– Rebuild those above a certain level of fragmentation
That kind of task is ALSO not too difficult to do in TSQL (Ola Hallengren is very well known for a TSQL based tool that does just this).
However, some tasks that aren’t easy to do in TSQL can be fairly easy to do with SMO, like (expect blog posts detailing all these things in the near future):
– Compare two databases on two servers and script out any tables or indexes on Server A that aren’t on Server B
– Script out all tables in a database that contain the string “Admin” in the table name.
– Compare users between two SQL instances and script out any variances.
You’ll notice that most of these are related to scripting and/or working across instances. If you’ve ever tried to script out a lot of objects from the SSMS GUI this probably should not be a surprise – while powerful, the GUI is a challenge to navigate especially in cases where you have a lot of non-default settings. In SMO, this is as easy as persisting a ScriptingOptions object that contains all your settings an reusing it.
So for now, consider this post a placeholder. I’ll cover at least those three scenarios above in upcoming posts, as well as anything else that I think may be useful.
Get-SQL 