Search code examples
c#sqlsmo

Smo user created stored procedure

When I try to get the list of stored procedures from db by using smo, it lists a lot of stored procedures even if the database is empty.

I want to be able to get a list of stored procedures that are not system procedures. IsSystemObject is not working. 

ServerConnection serverConnection = new ServerConnection(sqlConnection);
myserver = new Server(serverConnection);
Database mydb = new Database();
mydb = myserver.Databases[cmbDbname.Text];
string classGenerated = "";
foreach (StoredProcedure mystr in mydb.StoredProcedures)
{
    if (!mystr.IsSystemObject)
    {
        classGenerated += mystr.Name + Environment.NewLine;
    }
}
spClassText.Text = classGenerated;

Here, when I remove the !mystr.IsSystemObject clause, it returns a lot of stored procedures, else does not return my stored procedure created as a test.

Solution

  • Use the Schema-attribute like this:

    if (mystr.Schema != "sys")
{
    classGenerated += mystr.Name + Environment.NewLine;
}