Undocumented Extended and Stored Procedures

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

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

  • 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.

  • 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.

  • 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/

  • 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.

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

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

    Thanks for sharing the info

    David Bird

  • 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

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

    exec sp_who2 active

  • 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.

  • 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.

    Change is inevitable... Change for the better is not.


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

  • 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.

  • 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.

    Change is inevitable... Change for the better is not.


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

  • 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" ?

  • 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 51 total)

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