Some Undocumented Stored Procedures

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss Content Posted by Alexander Chigrik » Some Undocumented Stored Procedures

Some Undocumented Stored Procedures

Rate Topic

Display Mode

Topic Options

Author

Message

achigrik

Posted Thursday, August 09, 2001 12:00 AM

Grasshopper

Group: General Forum Members
Last Login: Saturday, September 21, 2002 12:00 AM
Points: 20, Visits: 1

Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/achigrik/someundocumentedstoredprocedures.asp

Post #730

Jeff Moden

Posted Monday, June 01, 2009 9:35 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:52 AM
Points: 32,893, Visits: 26,767

Heh... judging from the number of forums you have this same article posted on, I'd say you have some work cut out for you in making a correction. Wink

xp_DirTree actually takes 3 operands:
1. The path to a folder. This may be a UNC or drive path.
2. The number of levels below the path in the first operand. If missing, the default is "0" which equates to "all levels" which, as you know, can be terribly long. Levels above "0" operate as expected where Level 1 is the current directory only.
3. There's a third operand that most are simply not aware of. The default for this operand is "0" which means "only list directories". If this operand is set to any non-zero value, the output will return a 3rd column named "File". It should have been called "IsFile" because a "1" in this column means the "Subdirectory" column actually contains a file name instead of a folder name.

So, try running this and see what you get...
EXEC Master.dbo.xp_DirTree 'C:\',1,1

Think of it as an xp_FileExists on steroids. You can also capture the output for processing using something like the following:

CREATE TABLE #MyDir
(
RowNum INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
FolderFileName VARCHAR(128),
Depth TINYINT,
IsFile TINYINT
)

INSERT INTO #MyDir
(FolderFileName, Depth, IsFile)
EXEC Master.dbo.xp_DirTree 'C:\',1,1

SELECT * FROM #MyDir

Of course, the first operand may be contained in a variable without any dynamic SQL being necessary.

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

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #726761

« Prev Topic | Next Topic »

Permissions