sp_GetDirInfo
Returns information about the files in the specified directory and/or subdirectories (if requested) and the total bytes for those files. Directory names are excluded so that the total bytes can be computed using the compute clause but you can specify that they be included at the expense of no total bytes. This sp uses the bcp utility and tries to figure out the correct path when SQL 7.0 and SQL 2000 are both installed, however, you can include a bcp path parameter for non-standard installations.
Microsoft changed the output format results of "dir /a /X /-C /TC " from Windows 2000 to Windows 2003. This updates the code to reflect the changes so the code will work on either platform.
if exists (select *
from dbo.sysobjects
where id = object_id(N'[dbo].[sp_GetDirInfo]')
and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_GetDirInfo]
Go
Create procedure sp_GetDirInfo
@vcPrimaryPath varchar(255) = null,
@tiIncludeSubDir tinyint = 0,
@vcBcpPath varchar(255) = null,
@cDateType char(1) = 'C',
@tiIncludeDir tinyint = 0
As
/*******************************************************************/--Name : sp_GetDirInfo
--Server : Generic
--Description : Captures directory information into a table. Specify
-- a particular directory (and/or its subdirectories)
-- or gather all drive file information.
--
--Parameters : @vcPrimaryPath - Path of specified directory. Can be
-- a mapped drive that the SQL Server startup account
-- has to access. If null then all local drives are
-- included except the C drive. Use 1 to include C.
-- @vcBcpPath - Path to bcp.exe for the current SQL
-- version.
-- @tiIncludeSubDir - Set to 1 to include subdirectories.
-- @cDateType - Specifies whether to get the creation
-- date, last access date or the last written to date:
-- C - creation date, A - last access date, W - last
-- written date.
-- @tiIncludeDir - when 1 then includes directories in the
-- listing
--
--Note : Microsoft changed the output format of 'dir' with
-- certain switches which requires a change in the bcp
-- format file based on the Windows version.
--
--Date :
--Author : Clint Herring
--
--History :
/*******************************************************************/
Set nocount on
-- Declare variables.
Declare @vcCmd varchar(255),
@vcBcpFile varchar(255),
@vcPath varchar(255),
@vcFilePath varchar(255),
@dtDate datetime,
@iPos int,
@vcValue varchar(16),
@cVersion char(3),
@cWVersion char(3),
@vcCollation varchar(28),
@rtn int
Select @dtDate = getdate()
-- Create a table in msdb to hold directory info.
If exists(select * from msdb.dbo.sysobjects where name = 'filelist' and uid = 1)
Drop table msdb.dbo.filelist
-- What Windows version - 2k or 2003
If (PatIndex('%Windows NT 5.2%',@@version)) > 0 -- Win 2003
Begin
Exec ('Use msdb Create table dbo.filelist( ' +
'date varchar(10) null, ' +
'time varchar(14) null, ' +
'info varchar(15) null, ' +
'msdosname varchar(13) null, ' +
'filename varchar(54) null, ' +
'path varchar(255) null, ' +
'prcsd tinyint null)')
End
Else
Begin
Exec ('Use msdb Create table dbo.filelist( ' +
'date varchar(10) null, ' +
'time varchar(14) null, ' +
'info varchar(15) null, ' +
'msdosname varchar(16) null, ' +
'filename varchar(50) null, ' +
'path varchar(255) null, ' +
'prcsd tinyint null)')
End
-- If a temp table exists with the following name drop it.
If (Select object_id('tempdb.dbo.##fmtfile')) > 0
Exec ('Drop table ##fmtfile')
-- Create a temp table to load format file string information. Make
-- it a global temp table so we can bcp the data into a format file.
Create table ##fmtfile(id int identity(1,1) not null, string varchar(96) null)
-- If a temp table exists with the following name drop it.
If (Select object_id('tempdb.dbo.#fileexists')) > 0
Exec ('Drop table #fileexists')
-- Create a temp table to work with xp_fileexist.
Create table #fileexists(
FileExists tinyint null,
FileIsDirectory tinyint null,
ParentDirectoryExists tinyint null)
-- If a temp table exists with the following name drop it.
If (Select object_id('tempdb.dbo.#fixeddrives')) > 0
Exec ('Drop table #fixeddrives')
-- Create a temp table to work with xp_fileexist.
Create table #fixeddrives(
Drive char(1) null,
FreeSpace varchar(15) null)
-- If a temp table exists with the following name drop it.
If (Select object_id('tempdb.dbo.#DefaultBcpPath')) > 0
Exec ('Drop table #DefaultBcpPath')
-- Create a temp table to work with xp_fileexist.
Create table #DefaultBcpPath(Value varchar(255) null)
-- Check to see if we have enough space on drive C
Insert into #fixeddrives exec master.dbo.xp_fixeddrives
If (select convert(numeric(8), FreeSpace) from #fixeddrives where drive = 'C') < 1
Begin
Print 'There is not enough space on drive C to create temporary directory files.'
Print 'Please delete some files to make room before running this procedure again.'
Return
End
-- Check if we want to include the C drive with all other local drives
If @vcPrimaryPath = '1' -- means to include the C drive
Set @vcPrimaryPath = null
Else
Delete from #fixeddrives where drive = 'C'
-- Check SQL version
If (PatIndex('%7.00 - %',@@version) > 0) -- SQL 7.0
Begin
-- Set version specific variables
Set @cVersion = '6.0'
Set @vcCollation = ''
-- if @vcBcpPath is null then set a default bcp file path (must be short name).
If @vcBcpPath is null
-- Set the SQL 7.0 bcp.exe path
Select @vcBcpPath = 'c:\mssql7\binn\'
End
Else -- assume SQL 2000
Begin
-- Set version specific variables
Set @cVersion = '8.0'
Set @vcCollation = 'SQL_Latin1_General_CP1_CI_AS'
-- if @vcBcpPath is null then set a default bcp file path (must be short name).
If @vcBcpPath is null
Begin
-- Get the short path for 'Program Files'
Insert into #DefaultBcpPath
Exec @rtn = master.dbo.xp_cmdshell 'dir /X /AD c:\"Program file?"'
If @rtn <> 0 print 'Cannot get "Program file" short name.'
Select @iPos = charindex('~', value) from #DefaultBcpPath where value like '%Program files'
Select @vcBcpPath = 'c:\' + substring(value,@iPos-6, 8)
from #DefaultBcpPath
where value like '%Program files'
Truncate table #DefaultBcpPath
-- Get the short path for 'Microsoft SQL Server'
Insert into #DefaultBcpPath
Exec @rtn = master.dbo.xp_cmdshell 'dir /X /AD c:\"Program files"\"Microsoft SQL Serve?"'
If @rtn <> 0 print 'Cannot get "Microsoft SQL Server" short name.'
Select @iPos = charindex('~', value) from #DefaultBcpPath where value like '%Microsoft SQL Server'
Select @vcBcpPath = @vcBcpPath + '\' + substring(value,@iPos-6, 8) + '\80\tools\binn\'
from #DefaultBcpPath
where value like '%Microsoft SQL Server'
End
End
-- Check for bcp file.
Select @vcBcpFile = @vcBcpPath + 'bcp.exe'
Insert into #fileexists exec master.dbo.xp_fileexist @vcBcpFile
If (select ParentDirectoryExists from #fileexists) = 0
Begin
Select @vcCmd = 'The bcp.exe file path, ' + @vcBcpPath + 'bcp.exe, is not valid.'
Print @vcCmd
Return
End
Else
Begin
If (select FileExists from #fileexists) = 0
Begin
Select @vcCmd = 'The bcp.exe file cannot be found at ' + @vcBcpPath + '.'
Print @vcCmd
Return
End
Else
Begin
-- See if the path contains spaces
If (charindex(' ', @vcBcpPath) > 0)
Begin
Print 'The bcp path is valid but it contains spaces.'
Print 'This may cause some problems running bcp.'
Print 'Please replace the bcp path with the msdos or short path name.'
Return
End
End
End
-- Set variable path.
Select @vcPath = @vcPrimaryPath
-- Check the directory path variable.
If @vcPrimaryPath is null
Begin
-- The variable was null so get all fixed drives.
Insert into msdb.dbo.filelist
Select null, null, '<DIR>', 'Root','Root', Drive + ':\', null
from #fixeddrives
End
Else
Begin
-- We have a specific path.
Truncate table #fileexists
-- Remove double quotes for use with xp_fileexists.
Select @vcPath = Replace(@vcPrimaryPath, '"', '')
-- Insure we have a trailing '\'.
If right(@vcPrimaryPath,1) <> '\'
Begin
Select @vcPrimaryPath = @vcPrimaryPath + '\'
Select @vcPath = @vcPath + '\'
End
-- The variable is not null so check for a valid path.
Insert into #fileexists exec master.dbo.xp_fileexist @vcPrimaryPath
If ((select ParentDirectoryExists from #fileexists) = 0) or
((select ParentDirectoryExists from #fileexists) = 1 and
(select FileIsDirectory from #fileexists) = 0)
Begin
Select @vcCmd = 'The specified path does not exist.'
Print @vcCmd
Return
End
Else
Begin
-- See if the path contains spaces
If (charindex(' ', @vcPath) > 0 and charindex('"',@vcPrimaryPath) = 0)
Begin
Print 'The specified path is valid but it contains spaces.'
Print 'This may cause some problems running bcp.'
Print 'Please use double quotes around folder names that contain spaces.'
Return
End
Else
Begin
Insert into msdb.dbo.filelist
Values (null, null, '<DIR>', '<N/A>', '<Specified>', @vcPrimaryPath, null)
End
End
End
-- Build a format file for directory info.
-- What Windows version - 2k or 2003
If (PatIndex('%Windows NT 5.2%',@@version)) > 0 -- Win 2003
Begin
Insert into ##fmtfile(string) values(@cVersion)
Insert into ##fmtfile(string) values('7')
Insert into ##fmtfile(string) values('1 SQLCHAR 0 10 "" 1 date ' + @vcCollation)
Insert into ##fmtfile(string) values('2 SQLCHAR 0 14 "" 2 time ' + @vcCollation)
Insert into ##fmtfile(string) values('3 SQLCHAR 0 15 "" 3 info ' + @vcCollation)
Insert into ##fmtfile(string) values('4 SQLCHAR 0 13 "" 4 msdosname ' + @vcCollation)
Insert into ##fmtfile(string) values('5 SQLCHAR 0 54 "\r\n" 5 filename ' + @vcCollation)
Insert into ##fmtfile(string) values('6 SQLCHAR 0 0 "" 0 path ' + @vcCollation)
Insert into ##fmtfile(string) values('7 SQLCHAR 0 0 "" 0 prcsd ' + @vcCollation)
End
Else
Begin
Insert into ##fmtfile(string) values(@cVersion)
Insert into ##fmtfile(string) values('7')
Insert into ##fmtfile(string) values('1 SQLCHAR 0 10 "" 1 date ' + @vcCollation)
Insert into ##fmtfile(string) values('2 SQLCHAR 0 14 "" 2 time ' + @vcCollation)
Insert into ##fmtfile(string) values('3 SQLCHAR 0 15 "" 3 info ' + @vcCollation)
Insert into ##fmtfile(string) values('4 SQLCHAR 0 16 "" 4 msdosname ' + @vcCollation)
Insert into ##fmtfile(string) values('5 SQLCHAR 0 50 "\r\n" 5 filename ' + @vcCollation)
Insert into ##fmtfile(string) values('6 SQLCHAR 0 0 "" 0 path ' + @vcCollation)
Insert into ##fmtfile(string) values('7 SQLCHAR 0 0 "" 0 prcsd ' + @vcCollation)
End
-- select * from ##fmtfile order by id -- for testing
-- Bcp out the format file
Select @vcCmd = @vcBcpFile + ' "select string from ##fmtfile order by id" queryout c:\dir.fmt /c /S' + @@servername + ' /T' -- SQL 2K
-- Select @vcCmd -- for test
Exec @rtn = master.dbo.xp_cmdshell @vcCmd, no_output
If @rtn <> 0 print 'Failed to bcp out the fmt file.'
-- exec xp_cmdshell 'type c:\dir.fmt' -- for test
-- Get inital directory
Select @vcPath = min(path)
from msdb.dbo.filelist
where info = '<DIR>'
and prcsd is null
and charindex(' ', filename) = 0
-- Loop through all directories and subdirectories
While @vcPath is not null
Begin
-- create a text file containing directory info
Select @vcCmd = 'dir /a /X /-C /T' + @cDateType + ' ' + @vcPath +
'>c:\dir.txt' -- location of temp file
--Select @vcCmd -- for test
Exec @rtn = master.dbo.xp_cmdshell @vcCmd, no_output
If @rtn <> 0 print 'Failed to create the dir file for path ' + @vcPath
-- use findstr to create a headerless file with fixed length records
Select @vcCmd = 'findstr /i /r /c:"/../" c:\dir.txt>c:\fdir.txt'
--Select @vcCmd -- for test
Exec @rtn = master.dbo.xp_cmdshell @vcCmd, no_output
If @rtn <> 0 print 'Findstr failed.'
--exec xp_cmdshell 'type c:\fdir.txt' -- for test
-- bcp the directory data info msdb.dbo.filelist
Select @vcCmd = @vcBcpFile + ' msdb.dbo.filelist in ' +
'c:\fdir.txt /fc:\dir.fmt /S' + @@servername + ' /T '
--Select @vcCmd -- for test
Exec @rtn = master.dbo.xp_cmdshell @vcCmd, no_output
If @rtn <> 0 print 'Loading dir info failed.'
--select * from msdb.dbo.filelist -- for test
-- delete records whose filename matches any created files
Delete
from msdb.dbo.filelist
where filename = 'dir.txt'
or filename = 'fdir.txt'
or filename = 'dir.fmt'
or filename = '.'
or filename = '..'
-- Update the processed path
Update msdb.dbo.filelist
set prcsd = 1
where path = @vcPath
-- Update the path
Update msdb.dbo.filelist
set path = @vcPath +
case when charindex(' ', filename) > 0 and info = '<DIR>'
then '"' + filename + '"'
else filename
end +
case when info = '<DIR>' then '\' else '' end
Where path is null
skipit:
-- Stop if subdirectory info was not specified
-- and there are no more root directories.
If @tiIncludeSubDir = 0 or @tiIncludeSubDir is null
Begin
Select @vcPath = min(path)
from msdb.dbo.filelist
where info = '<DIR>'
and filename = 'root'
and prcsd is null
End
Else
Begin
-- Else get next directory path if any
Select @vcPath = min(path)
from msdb.dbo.filelist
where info = '<DIR>'
and prcsd is null
and filename <> 'System Volume Information'
End
End
-- Remove temp files.
Select @vcCmd = 'del c:\dir.txt'
Exec master.dbo.xp_cmdshell @vcCmd, no_output
Select @vcCmd = 'del c:\fdir.txt'
Exec master.dbo.xp_cmdshell @vcCmd, no_output
Select @vcCmd = 'del c:\dir.fmt'
Exec master.dbo.xp_cmdshell @vcCmd, no_output
-- select * from msdb.dbo.filelist --for test
-- Remove directories.
If @tiIncludeDir = 0
Delete from msdb.dbo.filelist where info = '<DIR>'
Set nocount off
-- If no file data display a message else return the data.
If (select count(*) from msdb.dbo.filelist) = 0
Begin
Print 'There are no files in the specified directories.'
End
Else
Begin
If @tiIncludeDir = 0
Begin
Select date,
time,
convert(numeric(15),info)[file size (bytes)],
filename,
path
from msdb.dbo.filelist
order by path, filename
compute sum(convert(numeric(15),info))
End
Else
Begin
Select date,
time,
info,
filename,
path
from msdb.dbo.filelist
order by path, filename
End
Set nocount on
Select str(datediff(ss, @dtDate, getdate())/60.0,8,3) + ' minutes' [Run Time]
End
Go
Grant Execute on dbo.sp_GetDirInfo to public
Go 
