question

EMooiw000 avatar image
EMooiw000 asked

CTE for directories

Hi, I need to get a summary of all files and their cumulative size per directory, as well as the total of all subdirectories in the parent, until I reach the root. I bump into several hurdles. 1) Not all directories are equally long in levels and therefor do not get taken into account with an anchor that selects on the maxlevel. 2) Summing of filesize and counting of files succeeds on a particular level, but I can't manage to take it up one level. 3) the recursion from bottom to top is not working as it should. Any suggestions on how to achieve this are greatly appreciated. Thanks, Eric
ctesumdirectory
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Can you post what code you have so far?
0 Likes 0 ·
KenJ avatar image
KenJ answered
It sounds like you're after some of the information that is available in the `DIR` command in the command shell. Since `DIR` already does the file counting and totals the sizes, I think you can just suck the output of that command into the database then query it to leave out the individual file information. This query uses xp_cmdshell and lists all of the directories along with file counts and cumulative file sizes for each directory in the tree with a total file count and size at the end (substring logic is specific to Windows NT 6.1 (Build 7601: Service Pack 1). I don't know if all versions of windows share the same format for the `DIR` command output). CREATE TABLE #directories (RowNumber INT IDENTITY(1,1), DirectoryText NVARCHAR(MAX)) INSERT INTO #directories EXEC xp_cmdshell 'dir c:\ /S' SELECT CASE WHEN DirectoryText LIKE ' Directory of %' THEN SUBSTRING(DirectoryText, 15, LEN(DirectoryText)) ELSE LTRIM(DirectoryText) END AS Directory FROM #directories WHERE DirectoryText LIKE ' Directory of %' OR DirectoryText LIKE ' Total Files Listed:%' OR SUBSTRING(DirectoryText, 18, 7) = 'File(s)' OR SUBSTRING(DirectoryText, 19, 7) = 'File(s)' ORDER BY RowNumber DROP TABLE #directories Here is some sample output: c:\ 13 File(s) 62,485,501 bytes c:\Default 0 File(s) 0 bytes c:\Default\256b44ef 7 File(s) 224,650 bytes c:\Default\5cedcae5 1 File(s) 15,994 bytes ... c:\Windows\wlansvc 0 File(s) 0 bytes c:\Windows\wlansvc\Policies 1 File(s) 4,660 bytes Total Files Listed: 445797 File(s) 123,449,890,890 bytes This was just for my C: drive, but you can specify any starting directory you'd like and it will walk the directory tree from there. Is this sort of what you are after? **[Edit]** I see you have some code already, but I thought I'd add the query to this that gives you counts by file type (the data was there already, just needed queried out): SELECT SUBSTRING(DirectoryText, LEN(DirectoryText) - CHARINDEX('.', REVERSE(DirectoryText))+1, LEN(DirectoryText)) AS Extension ,COUNT(*) AS FileCount FROM #directories WHERE DirectoryText NOT LIKE '%%' AND DirectoryText NOT LIKE '%Directory of%' AND DirectoryText LIKE '%.%' GROUP BY SUBSTRING(DirectoryText, LEN(DirectoryText) - CHARINDEX('.', REVERSE(DirectoryText))+1, LEN(DirectoryText)) ORDER BY SUBSTRING(DirectoryText, LEN(DirectoryText) - CHARINDEX('.', REVERSE(DirectoryText))+1, LEN(DirectoryText)) Along with some sample output: Extension FileCount .sql 55682 .sqlce 15 .sqlcmdvars 31 .sqldeployment 31 .sqlite 2 .sqlpermissions 31 .sqlpolicy 2 .sqlproj 11
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

EMooiw000 avatar image
EMooiw000 answered
Thanks Ken. This could work, but let in reality we will be collecting data on files from a number of locations, that will be pulled into sql table. so I would prefer to get the counts, also by filetype, in T-SQL, if at all possible. Below you'll find some source code DECLARE @ReportID Int, @MaxLevel Int, @StartDir varchar(100), @FoldersToExclude varchar(100) -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; -- Insert statements for procedure here set @reportid = 140 set @MaxLevel = 8 set @StartDir = '\\nt01\data' set @FoldersToExclude = 'Adobe OR Office\Files OR Etrust OR Office2007_SBE'; WITH NewFC (PName, PID, GPID, GPNAME, APP, Bytes, Aantal, Lvl ) AS ( --Anchor section SELECT DISTINCT TOP (100) PERCENT AP.R_PATH AS PName, AP.I_ASSET_ID AS PID, AP.I_PARENT_ASSET_ID AS GPID, CASE ap.r_objtype WHEN 'Folder' THEN reverse(RIGHT(reverse(ap.r_path), len(ap.r_path) - charindex('\', reverse(ap.r_path)))) WHEN 'File' THEN reverse(RIGHT(reverse(ap.r_path), len(ap.r_path) - charindex('\', reverse(ap.r_path)))) END AS GPName, FC1.APP, FC1.Bytes, FC1.Aantal, AP.I_LEVEL FROM dbo.ASSETS_PROPERTIES AP INNER JOIN Vw_FC_PID FC1 ON AP.I_ASSET_ID = FC1.PID WHERE (AP.R_OBJTYPE = 'Folder') AND (NOT CONTAINS(AP.R_PATH,@FoldersToExclude )) AND (AP.I_LEVEL = @MaxLevel) AND (I_REPORT=@ReportID) ORDER BY GPID, APP UNION ALL --Recursive Section SELECT AP2.R_PATH AS PName, AP2.I_ASSET_ID AS PID, AP2.I_PARENT_ASSET_ID AS GPID, CASE AP2.r_objtype WHEN 'Folder' THEN reverse(RIGHT(reverse(AP2.r_path), len(AP2.r_path) - charindex('\', reverse(AP2.r_path)))) WHEN 'File' THEN reverse(RIGHT(reverse(AP2.r_path), len(AP2.r_path) - charindex('\', reverse(AP2.r_path)))) END AS GPName, FC2.APP, FC2.Bytes + NewFC.Bytes, fc2.Aantal + NewFC.Aantal, AP2.I_LEVEL FROM dbo.ASSETS_PROPERTIES AP2 INNER JOIN Vw_FC_PID FC2 ON AP2.I_ASSET_ID = FC2.PID INNER JOIN NewFC ON Newfc.GPID = AP2.I_ASSET_ID AND NewFc.APP = fc2.app WHERE (AP2.R_OBJTYPE = 'Folder') AND (Ap2.I_REPORT=@ReportID) AND(NOT CONTAINS(Ap2.R_PATH,@FoldersToExclude )) AND (newfc.Lvl-1=ap2.i_level) ) SELECT * FROM NewFC ORDER BY lvl,PName END The table queried Assets_Properties includes among others, I_asset_id, I_parent_asset_id, I_report, I_level, R_pathname, filesize, filetype. the table includes folders as well as files. Also the user might select a small subselection and things would need to be recalculated then, without having access to the source. the vw_ft2app looks up the application of a filetype. for instance .xls, .xlsx, etc resolve to Excel. Hope this helps. Suggestions appreciated. Thanks Eric
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

So, you already have the directory contents pulled into `ASSETS_PROPERTIES`? Is this query pulling the results you need?
0 Likes 0 ·
Hi ken, data is in there, problem is the recursively adding of filesizes and counts from deepest directory to shallowest eg root, and including all files. Another reason to use thismtable is that not all sources will be filesystem.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.