Undocumented Extended and Stored Procedures

  • TimothyAWiseman

    SSCrazy Eights

    Points: 8819

    Comments posted to this topic are about the item Undocumented Extended and Stored Procedures

    ---
    Timothy A Wiseman
    SQL Blog: http://timothyawiseman.wordpress.com/

  • rnjohnson10

    SSC Journeyman

    Points: 96

    timothyawiseman (5/8/2008)


    Comments posted to this topic are about the item

    ...edited from article...

    Both sp_who and sp_who2 with its added columns can be tremendously valuable in scripts and programs, but for use on an ad hoc basis it is often more convenient to invoke the Activity Monitor GUI.

    ...edited from article...

    Are you kidding me? If something is hanging up the server, the GUI will take minutes or longer to show you what's happening. Using sp_who2 is generally pretty fast even if the server is getting creamed by something.

    I have a custom sp_who3 script which simply modifies the sp_who2 script to only show active processes. That way you can quickly see the active processes when something is slowing the server.

    I find the GUI to be worthless.

  • afryer

    Valued Member

    Points: 74

    I would use Powershell for the first one and maybe write my own SP for the second. It takes me back to my unix days when wee had a whole list of shell scripts that we laid over the OS.

  • TimothyAWiseman

    SSCrazy Eights

    Points: 8819

    rnjohnson10 (5/8/2008)

    Are you kidding me? If something is hanging up the server, the GUI will take minutes or longer to show you what's happening. Using sp_who2 is generally pretty fast even if the server is getting creamed by something.

    ....

    You have a good point. Sp_who and sp_who2 will always be faster than the gui and that is particularly true if something is causing a general slow down.

    With that said, I personally find the gui is often more convenient and more user friendly when looking for a specific block rather than a general slowdown. At least for me, both the procedures and the gui have their place depending on the situation.

    ---
    Timothy A Wiseman
    SQL Blog: http://timothyawiseman.wordpress.com/

  • Anatol Romanov-404520

    SSC Enthusiast

    Points: 122

    All extended stored procedures mentioned in the article are well known to most of the people working with SQL Server for some years. They have been discussed many times in SQL server books and on popular SQL Server websites. Google search on "undocumented sql server stored procedures" returned 89,000 web pages. xp_FileExists for example is discussed at sqlservercentral, developersdex, databasejournal, sqlteam, dbforums, etc. The article lacks novelty but may be useful for some community members. That's why I rated it as 3-star.

  • SuperDBA-207096

    SSCrazy Eights

    Points: 8176

    Nice work! Pretty comprehensive list, I've used most of these xp's in the past.

  • David Bird

    SSCarpal Tunnel

    Points: 4669

    Nice list of stored procedures. You pointed out a few I was unaware of.

    Thanks for sharing the info

    David Bird

  • Steve Eckhart

    SSCommitted

    Points: 1880

    Several of the examples were unreadable, displaying only a horizontal scroll bar. What might have been the example code was in a window which appeared to be 1 or 2 pixels tall.


    Steve Eckhart

  • Mark Yelton

    Default port

    Points: 1470

    Here's a caveat you can add to running sp_who2 to get just the running processes.

    exec sp_who2 active

  • mmendoza

    Valued Member

    Points: 71

    Steve Eckhart (5/9/2008)


    Several of the examples were unreadable, displaying only a horizontal scroll bar. What might have been the example code was in a window which appeared to be 1 or 2 pixels tall.

    I was able to work around this glitch by printing the article to PDF. The code listings are printed inline with the article.

    I agree with Steve that this UI bug should be fixed.

  • Jeff Moden

    SSC Guru

    Points: 994680

    Steve Eckhart (5/9/2008)


    Several of the examples were unreadable, displaying only a horizontal scroll bar. What might have been the example code was in a window which appeared to be 1 or 2 pixels tall.

    You can get the info by click selecting from just above the bad code window to just below it to select the window... the copy and pasted into a text editor.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

  • srienstr

    SSCrazy

    Points: 2251

    One comment:

    exec dbo.sp_msforeachtable 'delete test.dbo.[?]'

    There's generally no need to do a delete without criteria, as truncate will perform better with less logging:

    exec dbo.sp_msforeachtable 'truncate table test.dbo.[?]'

    That said, I'm sure it depends and there's a situation where the delete will be preferred for some reason, I just can't think of one in anything I've done.


    Puto me cogitare, ergo puto me esse.
    I think that I think, therefore I think that I am.

  • Jeff Moden

    SSC Guru

    Points: 994680

    srienstr (5/9/2008)


    One comment:

    exec dbo.sp_msforeachtable 'delete test.dbo.[?]'

    There's generally no need to do a delete without criteria, as truncate will perform better with less logging:

    exec dbo.sp_msforeachtable 'truncate table test.dbo.[?]'

    That said, I'm sure it depends and there's a situation where the delete will be preferred for some reason, I just can't think of one in anything I've done.

    One reason might be, you can't TRUNCATE if FK is present... heh, of course, that also means you need to delete in the correct table order.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

  • James Rochez

    SSCrazy

    Points: 2029

    XP_FileExist

    The usage is:

    EXEC xp_fileexist <filename> [, <file_exists INT> OUTPUT]

    If executed without providing an output variable, it will display 3 columns indicating whether the table passed exists, whether it is a directory, and if the parent directory exists.

    Do you mean "indicating whether the file passed exists" ?

  • srienstr

    SSCrazy

    Points: 2251

    Jeff Moden (5/9/2008)


    One reason might be, you can't TRUNCATE if FK is present... heh, of course, that also means you need to delete in the correct table order.

    Would using sp_MSdependencies provide a way for managing that? (I don't see a way to handle it without iterative processing, though it can at least be done using WHILE rather than a cursor)


    Puto me cogitare, ergo puto me esse.
    I think that I think, therefore I think that I am.

Viewing 15 posts - 1 through 15 (of 52 total)

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