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
