title: How do I list all non-system stored procedures?
body: <p>I want to create a query to list of all user defined stored procedures, excluding the ones that are system stored procedures, considering that:</p>
  
  <ol>
  <li>Checking the name like "sp_" doesn't work because there are user stored procedures that start with "sp_".</li>
  <li>Checking the property is_ms_shipped doesn't work because there are system stored procedures that have that flag = 0, for example: sp_alterdiagram (it is not MSShipped but appears under System Stored Procedures in SQL Server Management Studio).</li>
  </ol>
  
  <p>There must be a property, or a flag somewhere since you can see the "System Stored Procedures" in a separate folder in SQL 2005. Does anyone know?</p>
  
  <hr>
  
  <p>Edit: A combination of the suggestions below worked for me:</p>
  
  <pre><code>select *
  from 
      sys.objects             O LEFT OUTER JOIN
      sys.extended_properties E ON O.object_id = E.major_id
  WHERE
      O.name IS NOT NULL
      AND ISNULL(O.is_ms_shipped, 0) = 0
      AND ISNULL(E.name, '') &lt;&gt; 'microsoft_database_tools_support'
      AND O.type_desc = 'SQL_STORED_PROCEDURE'
  ORDER BY O.name
  </code></pre>