February 2, 2007 at 7:24 am
Hi,
We are using SMO to compare objects in our SQL Server database with another instance of sql server. I'm able to loop thourgh the stored procedures with no problem and retreave the names of them however it loops through all of the SPs even the system ones. This makes the loop take a while since it has to cycle through all of the system stored procedures. Is there a way to loop through only the dbo sps? I'm using VB.net
For
Each sp In theserver.Databases.Item(DBName).StoredProcedures
x = sp.ToString
If sp.IsSystemObject = False Then
'MsgBox(x)
End If
Next
Thanks
February 2, 2007 at 9:21 am
Hi, I found this article on this subject: http://sqljunkies.com/WebLog/euang/archive/2004/04/01/1889.aspx
The important bit being:
The addition of the If smoStoredProcedure.IsSystemObject = False test slows the execution of the code from 1 sec to 2 mins against Northwind. The slowdown is caused because the IsSystemObject Property is not part of the default properties we bring back when we populate the collection, so every call to the property results in a query being run to populate the property.
To improve the perf we have to add the following line before the collection is populated
Server.SetDefaultInitFields(GetType(Smo.StoredProcedure),"IsSystemObject")
Hope this helps!!
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply