THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Ben Miller

Using a typeof() in Powershell for SMO server.SetDefaultInitFields

In trying to squeeze performance out of the SMO stuff that I have been learning and doing for SQL Server I came across the method on the Server object called SetDefaultInitFields.  In my previous posts on Powershell or .NET and SMO, when you start scripting Views and Stored Procedures you start to get into areas where there are great amounts of System Objects.  In order to not script them out in the scripter, you either need to iterate over the collection or check IsSystemObject then script them, but either way you do it, you have to worry about the round trips to the server for more properties.

By default SMO gives minimal properties back to you from the server when going after collections of objects.  The SetDefaultInitFields allows you to specify a Type of object and a string array of properties to put in the return by default so that round tripping is not necessary. (See Michiel Wories blog post).  The challenge for me was to figure out how to use typeof() (C#) in Powershell.  I found a couple of references to creating a PS Add in which would work, but I just needed it to pass a type so that the method would work.

I found out in a forum post long ago (2006) that if you want to do a typeof(Microsoft.SqlServer.Management.SMO.StoredProcedure) then you would just do this :

$server.SetDefaultInitFields([Microsoft.SqlServer.Management.SMO.StoredProcedure], "IsSystemObject")

This line tells the server to return IsSystemObject as part of the properties that come back for the collection, which by default does not get returned, and when I query the property, it will get it from the object instead of asking the server about it, and it cut my execution time from 2 minutes to 10 seconds.  This is a great amount of savings when you have thousands of procedures and then you add in all the System Stored Procs, what a time killer and the SetDefaultInitFields is a time saver.

Published Tuesday, October 16, 2007 11:07 AM by dbaduck
Filed under:


No Comments
New Comments to this post are disabled

About dbaduck

Ben Miller is a Senior Database Administrator for HealthEquity in Draper, UT. He has been working with SQL since SQL Server 6.0 (1998) and has had a variety of roles in his career, including SQL Support and MVP Lead at Microsoft.
Privacy Statement