SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Undocumented Extended and Stored Procedures


Undocumented Extended and Stored Procedures

Author
Message
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87248 Visits: 41113
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

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
srienstr
srienstr
Old Hand
Old Hand (332 reputation)Old Hand (332 reputation)Old Hand (332 reputation)Old Hand (332 reputation)Old Hand (332 reputation)Old Hand (332 reputation)Old Hand (332 reputation)Old Hand (332 reputation)

Group: General Forum Members
Points: 332 Visits: 433
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
Jeff Moden
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87248 Visits: 41113
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

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
James Rochez
James Rochez
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1547 Visits: 824
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
srienstr
Old Hand
Old Hand (332 reputation)Old Hand (332 reputation)Old Hand (332 reputation)Old Hand (332 reputation)Old Hand (332 reputation)Old Hand (332 reputation)Old Hand (332 reputation)Old Hand (332 reputation)

Group: General Forum Members
Points: 332 Visits: 433
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.
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87248 Visits: 41113
srienstr (5/9/2008)
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)


Heh... I suppose... haven't used sp_MSdependencies in a long time. If you do use sp_MSdependencies, then your actually using a loop anyway. BigGrin

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

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Kevin Mao-243787
Kevin Mao-243787
SSC-Enthusiastic
SSC-Enthusiastic (192 reputation)SSC-Enthusiastic (192 reputation)SSC-Enthusiastic (192 reputation)SSC-Enthusiastic (192 reputation)SSC-Enthusiastic (192 reputation)SSC-Enthusiastic (192 reputation)SSC-Enthusiastic (192 reputation)SSC-Enthusiastic (192 reputation)

Group: General Forum Members
Points: 192 Visits: 183
Wow, that's great.
For the first extended store procedure xp_fileexist which exists in Sql 2000. Others are new in SQL 2005.
Thanks a lot. Smile
srienstr
srienstr
Old Hand
Old Hand (332 reputation)Old Hand (332 reputation)Old Hand (332 reputation)Old Hand (332 reputation)Old Hand (332 reputation)Old Hand (332 reputation)Old Hand (332 reputation)Old Hand (332 reputation)

Group: General Forum Members
Points: 332 Visits: 433
Kevin Mao (5/9/2008)
Wow, that's great.
For the first extended store procedure xp_fileexist which exists in Sql 2000. Others are new in SQL 2005.

Are you sure? I'm able to use sp_MSforeachdb and sp_MSforeachtable on my SQL Server 2000 instance.

[edit] I also see sp_MSdependencies, though I haven't used it.


Puto me cogitare, ergo puto me esse.
I think that I think, therefore I think that I am.
Charles Kincaid
Charles Kincaid
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1689 Visits: 2384
srienstr (5/9/2008)
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)


I just keep running the delete thing multiple times. I then TRUNCATE the log Hehe Of course if I want a realy clean small empty I just run my create scripts.

ATBCharles Kincaid
timothyawiseman
timothyawiseman
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1246 Visits: 920
Anatol Romanov (5/9/2008)
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.


You are absolutely right that these are all fairly well known amoungst certain communities of users, and I am certain that sp_who2 in particular is well known by most people who read this site. I certainly did not expect it to be breaking much new ground. Alexander Chigrik for instance has an excellent article on this same site on SQL Server 2000 undocumented procedures.

My primary motivation in writing this article was for my own team, who I found out in conversation was not aware of all of these. I went looking for a list that I could give to them, but I never found a single article that I was happy with for that purpose. Many were focused on SQL Server 2000, and there were some changes between 2000 and 2005. Others were too comprehensive and would have required my people to wade through lists of procedures which dealt primarily with replication which we do not currently use. Others only dealt with one of them instead of providing a complete list I wanted.

In the end I spent some time on a Saturday writing a list specifically for them that I thought would be useful to them. Then I decided there might be others who would find my particular list useful so I cleaned it up and submitted. Hopefully it will be of interest to others on this site.

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


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search