x002548's Blog

Not Just a Number - Brett Kaiser
posts - 89, comments - 586, trackbacks - 39

How do I use the DOS Command dir in T-SQL

Well, the short answer is you don't.  The modified answer is that you can use the code listed in this post. The longer answer is that you can use xp_cmdshell to do this (Which is what the stored procedure in this post does),  and must be either a member of the sysadmin account, or granted execution, say to a new role, and users can be placed in that role. 

There is a caveat to that.  From Books Online:

Execute permissions for xp_cmdshell default to members of the sysadmin fixed server role, but can be granted to other users.

Important  If you choose to use a Windows NT account that is not a member of the local administrator's group for the MSSQLServer service, users who are not members of the sysadmin fixed server role cannot execute xp_cmdshell.

Also, the long and short of it is, that having to build it, then to parse out the data, and to worry about security measures, I thought it might be a good idea just to create a stored procedure.  Now I'm sure someone may have done this already, but I thought I'd give it a shot.  This makes looking at your directories as easy as issuing the command EXEC Dir 'C:\*.*'.  Well you say, why not just do EXEC xp_cmdshell 'Dir C:\*.*'. Well for one, you won't have to grant your developers access to xp_cmdshell, so you gain more control.  A developer could reak havoc with your server. I've seen it.  Also, the information is now in a usable format.  Your developers can now call this stored procedure and place the results in to a table, or, (since it's all done with smoke and mirrors), they can access the underlying directory table.   As an afterthought, there are several commands that can be turned in to this type of functionality, and you can specialize them to “extend“ dos commands.  Currently I'm working on an archiving sproc that will make an archive directory for a directory supplied.  The idea is that when I'm done with all the files in my processing directory, I will move everything to the archive, so as to clean out the directory for the next processing cycle.  I'll post that when it's done.

One additional caveat is that this is Windows 2000 Server and up.  Windows NT produced different ouput results from the dir dos command.  If you need the old structure, let me know and I can post it.   I know there's away around this, I'm just not sure at the moment on how to do this.  If anyone remembers, I'd like to know.  Again thanks for reading.

 Here's the code, and good luck. 

CREATE TABLE [dbo].[Directory_Contents_Stage] (
   [dir]  varchar(255)
 , [dir_output]  varchar(255)
)
GO

CREATE TABLE [dbo].[Directory_Contents] (
   [dir]  varchar(255)
 , [Create_Time] datetime
 , [File_Size]  int
 , [File_Name]  varchar(255)
 , [Struct_Type] char(9)
)
GO

CREATE PROC Dir(@path varchar(2000), @Supress int = 0)
AS

SET NOCOUNT ON
TRUNCATE TABLE Directory_Contents_Stage

    DECLARE @cmd varchar(4000)
     SELECT @cmd = 'Dir "' + @path + '"'

INSERT INTO Directory_Contents_Stage(dir_output) EXEC master..xp_cmdshell @cmd

  IF EXISTS (SELECT * FROM Directory_Contents_Stage
       WHERE dir_output = 'The system cannot find the file specified.')
    BEGIN
         PRINT 'The system cannot find the file specified.'
         GOTO Dir_Error
    END

  IF EXISTS (SELECT * FROM Directory_Contents_Stage
       WHERE dir_output = 'File Not Found')
    BEGIN
         PRINT 'File Not Found'
         GOTO Dir_Error
    END         

  IF EXISTS (SELECT * FROM Directory_Contents_Stage
       WHERE dir_output = 'The system cannot find the path specified.')
    BEGIN
         PRINT 'The system cannot find the path specified.'
         GOTO Dir_Error
    END

     UPDATE Directory_Contents_Stage SET [dir] = @path

DELETE FROM Directory_Contents WHERE [dir] = @path

INSERT INTO Directory_Contents (Create_Time, File_Size, [File_Name], [dir], [Struct_Type])
     SELECT   CONVERT(datetime,SUBSTRING(dir_output,1,20)) AS [Create_Time]
     , CONVERT(int,LTRIM(RTRIM(REPLACE(SUBSTRING(dir_output,21,19),',','')))) AS [File_Size]
     , SUBSTRING(dir_output,40,(LEN(dir_output)-39)) AS [File_Name]
     , [dir]
     , 'FILE'
      FROM  Directory_Contents_Stage
     WHERE  SUBSTRING(dir_output,1,1) <> ' '
       AND (SUBSTRING(dir_output,1,1) <> ' '
       AND  SUBSTRING(dir_output,25,5) <> CHAR(60)+'Dir'+CHAR(62))

INSERT INTO Directory_Contents (Create_Time, [File_Name], [dir], [Struct_Type])
     SELECT   CONVERT(datetime,SUBSTRING(dir_output,1,20)) AS [Create_Time]
     , SUBSTRING(dir_output,40,(LEN(dir_output)-39)) As [File_Name]
     , [dir]
     , CHAR(60)+'Dir'+CHAR(62) AS  [Struct_Type]
       FROM  Directory_Contents_Stage
      WHERE  SUBSTRING(dir_output,25,5) = CHAR(60)+'Dir'+CHAR(62)

IF @Supress = 0

  SELECT [dir], Create_Time, File_Size, [File_Name], [Struct_Type]
    FROM Directory_Contents
   WHERE [dir] = @path
ORDER BY [Struct_Type] DESC, [Create_Time] DESC
GO

SET NOCOUNT OFF

Dir_Exit:
SET NOCOUNT OFF
RETURN

Dir_Error:
-- Add Error Handling
GOTO Dir_Exit
GO


EXEC Dir 'C:\*.*'
GO

DROP PROC Dir
GO

DROP TABLE Directory_Contents, Directory_Contents_Stage
GO

 

 

Print | posted on Tuesday, June 28, 2005 10:33 AM | Filed Under [ SQL Server ]

Feedback

Gravatar

# re: How do I use the DOS Command dir in T-SQL

I've got one here that might simplify this process a little:

http://weblogs.sqlteam.com/robv/articles/4107.aspx

You get a lot more control over the formatting of the directory output, and a little DOS redirection would allow you to use bcp or BULK INSERT too.
6/29/2005 8:30 PM | robvolk
Gravatar

# Thanks Rob

Thanks Rob, but how does that get the information into a table so you can utilize inside of T-SQL.

For example, I just finished an Archive stored procedure that utilizes the Dir stored procedure. It get's called in the stored procedure and checks for the existance of the files, then it will Archive everything in that existing directory, by adding a new timestamped Archive folder.

Do you just bcp the echoed results in?

6/30/2005 10:05 AM | Brett Kaiser
Gravatar

# Once I'm done processing files, How can I archive them?

6/30/2005 3:40 PM | x002548's Blog
Gravatar

# Once I'm done processing files, How can I archive them?

6/30/2005 3:44 PM | x002548's Blog
Gravatar

# re: How do I use the DOS Command dir in T-SQL

Yeah, you can bcp or BULK INSERT the echo'd results, or just INSERT...EXEC like the current proc does and parse it. It's easier in the fact that you don't have to deal with the differences in DIR output that varying operating systems have.

Another nicety is that FOR can recurse directories and present the full path and file information, which DIR cannot.
6/30/2005 8:30 PM | robvolk
Gravatar

# re: How do I use the DOS Command dir in T-SQL

Rob, I get an error. What am I doing wrong here.

This

EXE master..xp_cmdshell 'for %a in (*.*) do @echo %a,%~za,%~tam'

Produces

Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '.'.

Got an example?
7/1/2005 9:32 AM | Brett Kaiser
Gravatar

# re: How do I use the DOS Command dir in T-SQL

You, ummmmm, spelled "EXEC" wrong. :)
7/2/2005 11:06 AM | robvolk
Gravatar

# re: How do I use the DOS Command dir in T-SQL

Good lord

EXEC master..xp_cmdshell 'for %a in (*.*) do @echo %a,%~za,%~tam'


It seems like it finding ALL files, not just the ones in the root. Also since the data comes out in csv format, you'd probably need to use a function instead of just having to use substring. Plus there are no directory info there...

7/6/2005 9:08 AM | Brett Kaiser
Gravatar

# re: How do I use the DOS Command dir in T-SQL

You can modify the variable to include drive letter, path, etc., like so:

echo %~dpnxa,%~za,%~tam

And you can choose another character to delimit each section, not just a comma. And if you changed the output a little, you could format it as a fixed-width line that you can parse easily...it all depends on exactly what you need. All of these modfiers and examples are in the Windows help file.
7/6/2005 7:55 PM | robvolk
Gravatar

# re: How do I use the DOS Command dir in T-SQL

I want to more about Dos-command.
8/30/2005 1:12 AM | Rashed Ahmed
Gravatar

# re: How do I use the DOS Command dir in T-SQL

edit
8/30/2005 1:13 AM | Raj
Gravatar

# re: How do I use the DOS Command dir in T-SQL

Brett,

Do you mind if I site this entry on my blog (with full credit to you of course) ?

Thanks.
10/12/2005 2:21 AM | Greg Milner
Gravatar

# But of Course

Why no, but of course.....at least someone will post a reference and a link

http://weblogs.sqlteam.com/brettk/archive/2005/08/11/7516.aspx

Thanks

Brett

P.S. Please post a link of your blog here for circular references ;-)

10/12/2005 9:01 AM | Brett
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET