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.

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.

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.

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.

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)

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.

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)

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.