Post reply

How do I properly determine "system" stored procedures?

  • November 9, 2008 at 5:51 pm

    #131981

    I'm getting a list of stored procedures in a database by:

    select name from sysobjects o1

    where type in ('FN', 'IF', 'TF', 'P')

    and ObjectProperty(id, 'IsMSShipped') = 0

    (okay, I'm getting all functions also...)

    This code is returning the names of the following system stored procedures (as verified in SSMS as being in the "System Stored Procedures" folder for that database:

    sp_upgraddiagrams

    sp_helpdiagrams

    sp_helpdiagramdefinition

    sp_creatediagram

    sp_renamediagram

    sp_alterdiagram

    sp_dropdiagram

    How do I properly query the names of the non-system procs only?

    Thanks,

    Wayne

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes

    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • November 9, 2008 at 8:04 pm

    #896104

    Just add:

    And Not Left(name,3) = 'sp_'

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.     [/font]    [font="Verdana"] "Performance is our middle name."[/font]

  • November 10, 2008 at 5:29 am

    #896223

    I assume R.Barry Young was kidding ...

    select name from sysobjects o1

    where type in ('FN', 'IF', 'TF', 'P')

    and ObjectProperty(id, 'IsMSShipped') = 0

    and name not in (select name from sys.system_objects)

    Derek

  • November 10, 2008 at 6:47 am

    #896269

    Derek,

    This doesn't do it... those procs must not be in sys.system_objects

    Wayne

    Derek Dongray (11/10/2008)

    I assume R.Barry Young was kidding ...

    select name from sysobjects o1

    where type in ('FN', 'IF', 'TF', 'P')

    and ObjectProperty(id, 'IsMSShipped') = 0

    and name not in (select name from sys.system_objects)

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes

    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • November 10, 2008 at 7:42 am

    #896305

    Okay, I found out how SSMS is doing it (by tracing it's actions).

    It needs to be done like:

    select name from sysobjects o1

    where type in ('FN', 'IF', 'TF', 'P')

    and ObjectProperty(id, 'IsMSShipped') = 0

    and id not in (select major_id from sys.extended_properties where name = N'microsoft_database_tools_support' and minor_id = 0 and class = 1)

    Seems kinda hokey to me... MS should have these procs marked as being IsMSShipped.

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes

    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • November 10, 2008 at 8:39 am

    #896341

    WayneS (11/10/2008)

    Seems kinda hokey to me... MS should have these procs marked as being IsMSShipped.

    The flag 'isMSShipped' simply indicates that something existed in the database when it was created, i.e. it was shipped as part of the installed system.

    It is possible to add procedures and get the defined as system procedures at a later date. There is an undocumented procedure, sys.sp_MS_marksystemobject, which will mark a procedure as being a system procedure ...

    USE master

    GO

    CREATE PROC sp_generate_inserts

    AS

    BEGIN

    --

    -- code omitted for brevity

    --

    END

    GO

    --Mark procedure as system object

    EXEC sys.sp_MS_marksystemobject sp_generate_inserts

    GOAfter executing, the procedure sp_generate_inserts will be a system procedure but 'isMSShipped' will be 0. This is obviously the desired behaviour, since the procedure is not shipped by MS. 🙂

    Derek

  • November 10, 2008 at 4:43 pm

    #896647

    Derek Dongray (11/10/2008)

    I assume R.Barry Young was kidding ...

    Not really, "Not Left(name,3) = 'sp_'" is actually what I use. I've never had a problem with it.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.     [/font]    [font="Verdana"] "Performance is our middle name."[/font]

  • November 11, 2008 at 2:02 am

    #896798

    rbarryyoung (11/10/2008)

    Derek Dongray (11/10/2008)

    I assume R.Barry Young was kidding ...

    Not really, "Not Left(name,3) = 'sp_'" is actually what I use. I've never had a problem with it.

    Barry,

    you must nbe a really lucky DBA. Personally I've seen way to much user procedures using the "sp_" prefix to even think about using your query.

    [font="Verdana"]Markus Bohse[/font]

  • November 11, 2008 at 2:13 am

    #896804

    rbarryyoung (11/10/2008)

    Derek Dongray (11/10/2008)

    I assume R.Barry Young was kidding ...

    Not really, "Not Left(name,3) = 'sp_'" is actually what I use. I've never had a problem with it.

    Unfortunately, in the past, I've inherited applications with a large number of user procedures with names starting 'sp_'. I've tried to educate the relevant people, but without success to date. 🙁

    Of course, you are actually right (according to Microsoft's guidelines http://msdn.microsoft.com/en-us/library/ms190669.aspx) but SQL server doesn't actually stop people from doing it and doesn't even issue a warning! So some people have got into very bad habits.

    Derek

  • November 11, 2008 at 6:23 am

    #896899

    Derek Dongray (11/11/2008)

    rbarryyoung (11/10/2008)

    Derek Dongray (11/10/2008)

    I assume R.Barry Young was kidding ...

    Not really, "Not Left(name,3) = 'sp_'" is actually what I use. I've never had a problem with it.

    Unfortunately, in the past, I've inherited applications with a large number of user procedures with names starting 'sp_'. I've tried to educate the relevant people, but without success to date. 🙁

    Of course, you are actually right (according to Microsoft's guidelines http://msdn.microsoft.com/en-us/library/ms190669.aspx) but SQL server doesn't actually stop people from doing it and doesn't even issue a warning! So some people have got into very bad habits.

    SQL Server may not stop it, but I do.

    I do understand the inheritance problem though, but this is a site-by-site issue. Happily, most sites I have worked at it has not been an issue.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.     [/font]    [font="Verdana"] "Performance is our middle name."[/font]

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply