Undocumented Extended and Stored Procedures

Question

Undocumented Extended and Stored Procedures

TimothyAWiseman

SSCrazy Eights

Points: 8819
More actions
May 8, 2008 at 11:19 pm

#186307

Comments posted to this topic are about the item Undocumented Extended and Stored Procedures
---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/

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

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

rnjohnson10 SSC Journeyman Points: 96 More actions · Answer 1

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 More actions · Answer 2

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 More actions · Answer 3

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 More actions · Answer 4

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 More actions · Answer 5

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

David Bird SSCarpal Tunnel Points: 4669 More actions · Answer 6

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 More actions · Answer 7

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 SSCommitted Points: 1570 More actions · Answer 8

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 More actions · Answer 9

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: 1004329 More actions · Answer 10

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)

srienstr SSCrazy Points: 2435 More actions · Answer 11

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: 1004329 More actions · Answer 12

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)

James Rochez SSCrazy Points: 2029 More actions · Answer 13

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: 2435 More actions · Answer 14

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.