Last post Nov 16, 2015 12:18 AM by Nan Yu
Nov 13, 2015 12:33 PM|ejowens|LINK
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?
Nov 13, 2015 02:52 PM|limno|LINK
select * from sys.objects
Nov 13, 2015 04:40 PM|ejowens|LINK
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.
Nov 16, 2015 12:18 AM|Nan Yu|LINK
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 (
WHERE SCHEMA_NAME(schema_id) = 'sys'
OR is_ms_shipped = 1);
Refer to below link for more details:
Hope this helps.