1
SELECT DISTINCT 
OBJECT_SCHEMA_NAME (sc.object_id) as "schema", OBJECT_NAME(sc.object_id) as "name", sc.*   
-- FROM syscomments sc 
FROM sys.sql_modules sc 
WHERE "Definition" LIKE '%raiserror%'
and
OBJECTPROPERTY(object_id, 'IsMSShipped') = 0
and
OBJECT_NAME(sc.object_id) like '%diagram%'

Why is this query returning these SPs? Aren't they from Microsoft?

sp_helpdiagramdefinition
sp_creatediagram
sp_renamediagram
sp_alterdiagram
sp_dropdiagram

jarlh
  • 42,561
  • 8
  • 45
  • 63
peter.petrov
  • 38,363
  • 16
  • 94
  • 159
  • They dont come up when I run that command.. although I would agree they sound like MS ones – BugFinder Jun 14 '18 at 13:18
  • 1
    @BugFinder They're optional. They only get installed if you click on the [Database Diagrams] folder in SSMS. – RBarryYoung Jun 14 '18 at 13:25
  • The diagramming objects live in a twilight zone where they're "sort of" system objects, but also "sort of" not since Management Studio is the only thing that creates and uses them (and that only on demand). It chooses not to mark them as system objects, although it could. As far as SQL Server itself is concerned, these are no more special than your average user object. (And the fact that their names start with `sp_` is arguably a mistake, same as for any other user-defined stored procedure.) – Jeroen Mostert Jun 14 '18 at 13:35

1 Answers1

1

IsMSShipped is set to 1 for any object that was created during SQL Server's installation. The Diagram objects are optional and are only added to a database after the initial installation.

In other words, although they are from MS, they are not Shipped from MS (at least not as MS is defining "Shipped").

Yes I know, it's dumb, everyone gets tripped up by this at least once. They should have called it something like IsMSInstalled instead. Just goes to show the importance of picking good names.

The SOP way to handle this is to also filter on the schema ("sys" is always schema_id 4).

RBarryYoung
  • 55,398
  • 14
  • 96
  • 137
  • IsMSShipped is also set for e.g. replication objects which are only added to DBs after their initial creation (or at least, this was certainly true in the ~2008 time frame. Haven't re-verified recently) – Damien_The_Unbeliever Jun 14 '18 at 13:22
  • You can also tag your procedures as MSShipped through the undocumented sp_MS_marksystemobject. – EzLo Jun 14 '18 at 13:27
  • 1
    But in my case it says "dbo", not "sys" for schema name. – peter.petrov Jun 14 '18 at 13:28
  • 1
    See [this accepted answer from 2011](https://stackoverflow.com/a/6521747/15498) where we were discussing that replication objects do get marked as MS Shipped. – Damien_The_Unbeliever Jun 14 '18 at 13:28
  • @peter.petrov Yeah, you're right. I forgot that the Diagram objects are grandfathered (or something) into .dbo. I have to add a special filter for '*diagram*'. Actually, I hate the diagram objects and I try to make sure they aren't in any actual DB that I am using. If I need the diagram facility, I make a metadata-only copy of my DB and use it there. – RBarryYoung Jun 14 '18 at 13:35
  • @Damien_The_Unbeliever Yep, I forgot that too. Basically, `IsMSShipped` isn't very consistent at all. – RBarryYoung Jun 14 '18 at 13:36