Looping through non system database objects using SMO and VB.net

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

Hi,

see this here:

http://www.sqlteam.com/item.asp?ItemID=23185

The following can be used to test your code against your databases:

For one of my databases with 902 Procedures this was a huge difference.

using System;

using Microsoft.SqlServer.Management.Smo;

using Microsoft.Win32;

namespace SMOProject

{

class Program

{

static void Main(string[] args)

{

EvaluateSMOTime(".", "SQLSErver2005", true);

EvaluateSMOTime(".", "SQLSErver2005", false);

Console.ReadLine();

}

internal static void EvaluateSMOTime(string ServerName, string DatabaseBaseName, bool SetDefaultField)

{

DateTime Before = DateTime.Now;

Server theServer = new Server(ServerName);

if (SetDefaultField)

theServer.SetDefaultInitFields(typeof(StoredProcedure), "IsSystemObject");

Database myDB = theServer.Databases[DatabaseBaseName];

foreach (StoredProcedure sp in myDB.StoredProcedures)

{

if (!sp.IsSystemObject)

{

Console.Write(".");

}

}

DateTime After = DateTime.Now;

TimeSpan Diff = After.Subtract(Before);

Console.WriteLine(string.Format("With{1} tweaking the DefaultInitFields : {2} ms", Diff.Milliseconds, SetDefaultField ? string.Empty : "on"));

}

}

}

HTH, Jens K. Suessmeyer.

http://www.sqlserver20005.de