locked
SQL Server 2008 R2 and User Objects in sys.objects RRS feed

  • Question

  • User120353699 posted

    Hey, folks, thanks for the forums and any advice you can offer!

    As the subject line indicates, I am using SQL Server 2008 R2. 

    I need to find all the user objects in sys.objects, that is, all non-system objects.  I do not care which specific user created the object.

    The problem I am having, though, is with the stored procedures.  The database has a diagram and when that diagram was first created, SQL Server created all the objects necessary to support diagrams -- about 7 of them (for example "sp_dropdiagram").

    When I look at sys.objects, these stored procedures that were created to support diagrams, have the same type, is_ms_shipped, schema_id, etc as all the user created objects (to me, this is not a user created object -- SQL Server created these objects, regardless of when it was told to create them).  This means there is no way to actually differentiate which stored procedures are actually created by a user, without first knowing the names of the other stored procedure objects.


    Any thoughts on how to differentiate?

    Friday, November 13, 2015 12:33 PM

Answers

  • User1724605321 posted

    Hi ejowens,

    Yes ,is_ms_shipped is not always 100% reliable. Maybe you could use this approach, which is a combination of is_ms_shipped and the 'sys' schema name:

    SELECT * FROM sys.objects 
    WHERE SCHEMA_NAME(schema_id) <> 'sys'  
    AND is_ms_shipped = 0  
    AND parent_object_id NOT IN (
     SELECT object_id  
     FROM sys.objects  
     WHERE SCHEMA_NAME(schema_id) = 'sys'  
            OR is_ms_shipped = 1); 
    

    Refer to below link for more details:

    http://www.sqlfingers.com/2015/01/find-all-user-created-objects-in-your.html

    Hope this helps.

    Best Regards,

    Nan Yu

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, November 16, 2015 12:18 AM

All replies

  • User77042963 posted
    select * from sys.objects 
    Where is_ms_shipped=0

    Friday, November 13, 2015 2:52 PM
  • User120353699 posted

    I appreciate the help, but that does not work. 

    If you re-read my OP, I already indicated that the is_ms_shipped field all have the same values for the diagram stored procedures as user created stored procedures; that is, a 0.

    Friday, November 13, 2015 4:40 PM
  • User1724605321 posted

    Hi ejowens,

    Yes ,is_ms_shipped is not always 100% reliable. Maybe you could use this approach, which is a combination of is_ms_shipped and the 'sys' schema name:

    SELECT * FROM sys.objects 
    WHERE SCHEMA_NAME(schema_id) <> 'sys'  
    AND is_ms_shipped = 0  
    AND parent_object_id NOT IN (
     SELECT object_id  
     FROM sys.objects  
     WHERE SCHEMA_NAME(schema_id) = 'sys'  
            OR is_ms_shipped = 1); 
    

    Refer to below link for more details:

    http://www.sqlfingers.com/2015/01/find-all-user-created-objects-in-your.html

    Hope this helps.

    Best Regards,

    Nan Yu

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, November 16, 2015 12:18 AM