Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12345»»»

Undocumented Extended and Stored Procedures Expand / Collapse
Author
Message
Posted Friday, May 9, 2008 8:44 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 10:12 AM
Points: 35,589, Visits: 32,179
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #497948
Posted Friday, May 9, 2008 9:09 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, August 13, 2013 11:43 AM
Points: 242, 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.
Post #497977
Posted Friday, May 9, 2008 9:16 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 10:12 AM
Points: 35,589, Visits: 32,179
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #497986
Posted Friday, May 9, 2008 9:26 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, April 30, 2013 9:07 PM
Points: 1,529, 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" ?



Post #497998
Posted Friday, May 9, 2008 9:35 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, August 13, 2013 11:43 AM
Points: 242, 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.
Post #498008
Posted Friday, May 9, 2008 9:50 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 10:12 AM
Points: 35,589, Visits: 32,179
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. :D


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

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #498031
Posted Friday, May 9, 2008 12:30 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, June 26, 2012 1:35 PM
Points: 170, 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. :)

Post #498157
Posted Friday, May 9, 2008 12:38 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, August 13, 2013 11:43 AM
Points: 242, 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.
Post #498161
Posted Friday, May 9, 2008 12:58 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 10:41 AM
Points: 809, Visits: 1,996
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 Of course if I want a realy clean small empty I just run my create scripts.


ATB

Charles Kincaid

Post #498168
Posted Friday, May 9, 2008 6:02 PM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: 2 days ago @ 4:29 PM
Points: 752, 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/
Post #498289
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse