May 9, 2008 at 9:50 am
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. 😀
--Jeff Moden
Change is inevitable... Change for the better is not.
May 9, 2008 at 12:30 pm
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. 🙂
May 9, 2008 at 12:38 pm
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.
May 9, 2008 at 12:58 pm
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
May 9, 2008 at 6:02 pm
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/
May 9, 2008 at 6:04 pm
James Rochez (5/9/2008)
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" ?
Yes. Sorry I missed that one when I edited.
---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/
May 10, 2008 at 1:58 am
The article has a good list of stored procedures. I was not aware of some of the procedures.
🙂
May 10, 2008 at 7:32 am
This is the same thing that I just went through... I thought for sure that my latest article was on something very obvious. It turns out that it's not so obvious to a large part of the community and several of the "old dogs" appreciated the "reminder". Further, there are a lot of "newbies" that enter the deep blue realm of SQL server every day. These type of articles are perfect for them, as well.
Well done Timothy! Keep them coming!
--Jeff Moden
Change is inevitable... Change for the better is not.
May 12, 2008 at 8:48 am
OK. On that same thought here is another reminder. Always test your code all over again in a test environment before rolling out an upgrade, a service pack, or even a hotfix. This is especially true if you are doing ticks and special ops like using undocumented features.
ATBCharles Kincaid
November 20, 2009 at 1:25 am
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.
Um, so who mentioned anything about a hung server? We have the DAC for that. 2005 Activity Monitor was pretty good, all things considered. Try 2008 Activity Monitor (which really does suck!) and prepare to miss the 2005 version.
rnjohnson10 (5/8/2008)
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.
Well done you! MVP Adam Machanic also has a very fine piece of work available for download called sp_whoisactive. I recommend it - even if you just look at some of the awesome techniques Adam uses within the procedure.
rnjohnson10 (5/8/2008)
I find the GUI to be worthless.
Harsh.
November 20, 2009 at 1:29 am
Anatol Romanov-404520 (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.
I rated it 4 stars. Not that anyone should care about your or my ratings at all. I think Timothy did an excellent job with it. As Jeff mentions, just because you have heard of some of these things, doesn't mean everyone (or even most people) have. I look forward to reviewing your first article Mr Romanov 😀
November 20, 2009 at 1:35 am
srienstr (5/9/2008)
One comment: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.
Tables referenced by a foreign key (as Jeff mentioned)
Tables referenced by an indexed view
Tables published for transactional or merge replication
TRUNCATE TABLE does not fire triggers (desirable sometimes, other times not so much).
Also, TRUNCATE TABLE requires the fairly heavy-duty ALTER TABLE permission. This requirement is often unpopular with DBAs who like to keep granted permissions to a minimum. It is particularly inconvenient in stored procedures.
Paul
November 20, 2009 at 1:38 am
Charles Kincaid (5/9/2008)
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.
You truncate the log? Do you mean that you backup the log, switch to the SIMPLE recovery model, run a CHECKPOINT, switch back to FULL recovery, and immediately take a full database backup? 😉
November 20, 2009 at 1:40 am
timothyawiseman (5/9/2008)
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.
Good call! A great reason to write an article, and I applaud your altruism.
November 20, 2009 at 6:29 am
where can we get the list of undocumented stored procedures?
can you collect and publish here?
Viewing 15 posts - 16 through 30 (of 51 total)
You must be logged in to reply to this topic. Login to reply