December 8, 2014 at 12:55 am
hello all.
I have this script:
IF OBJECT_ID('tempdb..#DirectoryTree') IS NOT NULL
DROP TABLE #DirectoryTree;
CREATE TABLE #DirectoryTree (
id int IDENTITY(1,1)
,subdirectory nvarchar(512)
,depth int
,isfile bit
, ParentDirectory int
,flag tinyint default(0));
INSERT #DirectoryTree (subdirectory,depth,isfile)
EXEC master.sys.xp_dirtree 'E:\ElectronicArchieve',0,1;
UPDATE #DirectoryTree
SET ParentDirectory = (
SELECT MAX(Id) FROM #DirectoryTree
WHERE Depth = d.Depth - 1 AND Id < d.Id
)
FROM #DirectoryTree d;
WITH CTE as (
SELECT
Id, CAST(SubDirectory as nvarchar(255)) as SubDirectory,
Depth, ParentDirectory, CAST('' as nvarchar(255)) as Parent,isfile,flag
FROM #DirectoryTree WHERE ParentDirectory IS NULL
UNION ALL
SELECT
d.Id, CAST(d.SubDirectory as nvarchar(255)),
d.Depth, d.ParentDirectory, CAST(CTE.SubDirectory as nvarchar(255)),d.isfile,d.flag
FROM #DirectoryTree d
INNER JOIN CTE ON d.ParentDirectory = CTE.Id
)
SELECT * FROM CTE order by id
now I want to have file's full path, I mean I want to have below Result:
id subdirectory depth parentDirectory parent isfile flag fullpath
1 a 1 null 0 0 E:\ElectronicArchieve\a
2 b 2 1 a 0 0 E:\ElectronicArchieve\a\b
3 c.jpg 3 2 b 0 0 E:\ElectronicArchieve\a\b\c.jpg
How can I have this Result?please correct my code.thanks
December 8, 2014 at 2:12 am
I found this script but It does not return my result:
IF OBJECT_ID('tempdb..#dirtree') IS NOT NULL
DROP TABLE #dirtree;
CREATE TABLE #dirtree
(
id INT identity(1,1),
subdirectory NVARCHAR(260),
depth INT ,
is_file BIT,
parentId INT
)
INSERT INTO #dirtree(subdirectory,depth,is_file)
EXEC master.sys.xp_dirtree 'E:\ElectronicArchieve',0,1;
UPDATE #dirtree
SET ParentId = (SELECT MAX(Id) FROM #dirtree
WHERE Depth = T1.Depth - 1 AND Id < T1.Id)
FROM #dirtree T1
--select * from #dirtree
;WITH CTE
AS
(
SELECT
t.id,
t.subdirectory,
t.depth,
t.is_file
FROM
#dirtree AS t
WHERE
is_file=0
UNION ALL
SELECT
t.id,
CAST(CTE.subdirectory+'\'+t.subdirectory AS NVARCHAR(260)),
t.depth,
t.is_file
FROM
#dirtree AS t
JOIN CTE
ON CTE.id=t.parentId
)
--select * from CTE
SELECT
'E:\ElectronicArchieve\'+CTE.subdirectory AS [path]
FROM
CTE
WHERE
CTE.is_file=1
UNION ALL
SELECT
'E:\ElectronicArchieve\'+t.subdirectory
FROM
#dirtree AS t
WHERE
is_file=1
AND NOT EXISTS
(
SELECT
NULL
FROM
CTE
WHERE
CTE.id=t.id
)
-- -------------------------------------------------------------
-- /*
-- traverse directory tree and get back complete list of filenames w/ their paths
--*/
--declare
-- @dirRoot varchar(255)='E:\ElectronicArchieve'
--declare
-- @sqlCmd varchar(255),
-- @idx int,
-- @dirSearch varchar(255)
--declare @directories table(directoryName varchar(255), depth int, isfile int, rootName varchar(255),rowid int identity(1,1))
--insert into @directories(directoryName, depth,isFile)
--exec master.sys.xp_dirtree @dirRoot,0,1
--if not exists(select * from @directories)
-- return
--update @directories
--set rootName = @dirRoot + '\' + directoryName
---- traverse from root directory
--select @idx=min(rowId) from @directories
---- forever always ends too soon
--while 1=1
--begin
-- select @dirSearch = rootName
-- from @directories
-- where rowid=@idx
-- insert into @directories(directoryName, depth,isfile)
-- exec master.sys.xp_dirtree @dirSearch,1,1
-- update @directories
-- set rootName = @dirSearch + '\' + directoryName
-- where rootName is null
-- set @idx = @idx + 1
-- -- you see what i mean don't you?
-- if @idx > (select max(rowid) from @directories) or @idx is null
-- break
--end
-- select
-- case isFile when 0 then 'Directory' else 'File' end [attribute],
-- rootName [filePath]
-- from @directories
-- order by filePath
December 8, 2014 at 3:30 am
I'm a bit confused.
If you already have loaded dir structure into archive as it was discussed here http://www.sqlservercentral.com/Forums/Topic1640110-391-1.aspx then you can just run the last query in the batch to get files and full path for every file.
If you need directory structure without loading it to archive mentioned above, tweak that CTE to make use of temp table directly.
December 8, 2014 at 3:55 am
I need full path as a filed in first query.please correct first query for this goal.in adition to I run last query but it does not get my goal.
December 8, 2014 at 4:55 am
This batch will show all objects (directories, files) and their full paths
--parameter
declare @myPath nvarchar(4000) = 'E:\ElectronicArchieve';
IF OBJECT_ID('tempdb..#DirectoryTree') IS NOT NULL
DROP TABLE #DirectoryTree;
CREATE TABLE #DirectoryTree (
id int IDENTITY(1,1)
,subdirectory nvarchar(512)
,depth int
,isfile bit
, ParentDirectory int
,flag tinyint default(0));
-- top level directory
INSERT #DirectoryTree (subdirectory,depth,isfile)
VALUES (@myPath,0,0);
-- all the rest under top level
INSERT #DirectoryTree (subdirectory,depth,isfile)
EXEC master.sys.xp_dirtree @myPath,0,1;
UPDATE #DirectoryTree
SET ParentDirectory = (
SELECT MAX(Id) FROM #DirectoryTree
WHERE Depth = d.Depth - 1 AND Id < d.Id)
FROM #DirectoryTree d;
-- SEE all with full paths
WITH dirs AS (
SELECT
Id,subdirectory,depth,isfile,ParentDirectory,flag
, CAST (null AS NVARCHAR(MAX)) AS container
, CAST([subdirectory] AS NVARCHAR(MAX)) AS dpath
FROM #DirectoryTree
WHERE ParentDirectory IS NULL
UNION ALL
SELECT
d.Id,d.subdirectory,d.depth,d.isfile,d.ParentDirectory,d.flag
, dpath as container
, dpath +'\'+d.[subdirectory]
FROM #DirectoryTree AS d
INNER JOIN dirs ON d.ParentDirectory = dirs.id
)
SELECT * FROM dirs
-- Dir style ordering
ORDER BY container, isfile, subdirectory
hope it helps.
December 8, 2014 at 5:13 am
thank you so much dear serg.It is my goal completely. thank you so much
December 8, 2014 at 5:32 am
just one point dear serg: Because my files' name are Persian, dpath is disarranged.how can I solve my problem with Persian name?
December 8, 2014 at 5:40 am
Specify proper collation for the column in ORDER BY
See http://msdn.microsoft.com/ru-ru/library/ms184391.aspx
Not sure which one is for Persian.
December 9, 2014 at 4:02 am
hello dear serg.
I have changed your code by COLLATE Persian_100_CI_AS but I still have problem:
declare @myPath nvarchar(4000) = 'E:\ElectronicArchieve';
IF OBJECT_ID('tempdb..#DirectoryTree') IS NOT NULL
DROP TABLE #DirectoryTree;
CREATE TABLE #DirectoryTree (
id int IDENTITY(1,1)
,subdirectory nvarchar(512) COLLATE Persian_100_CI_AS
,depth int
,isfile bit
,ParentDirectory int
,flag tinyint default(0));
-- top level directory
INSERT #DirectoryTree (subdirectory,depth,isfile)
VALUES (@myPath,0,0);
-- all the rest under top level
INSERT #DirectoryTree (subdirectory,depth,isfile)
EXEC master.sys.xp_dirtree @myPath,0,1;
UPDATE #DirectoryTree
SET ParentDirectory = (
SELECT MAX(Id) FROM #DirectoryTree
WHERE Depth = d.Depth - 1 AND Id < d.Id)
FROM #DirectoryTree d;
-- SEE all with full paths
WITH dirs AS (
SELECT
Id,subdirectory,depth,isfile,ParentDirectory,flag
,CAST (null AS NVARCHAR(MAX)) COLLATE Persian_100_CI_AS AS container
,CAST([subdirectory] AS NVARCHAR(MAX)) COLLATE Persian_100_CI_AS AS dpath
FROM #DirectoryTree
WHERE ParentDirectory IS NULL
UNION ALL
SELECT
d.Id,d.subdirectory,d.depth,d.isfile,d.ParentDirectory,d.flag
, dpath COLLATE Persian_100_CI_AS as container
, dpath COLLATE Persian_100_CI_AS+'\'+ d.[subdirectory] COLLATE Persian_100_CI_AS
FROM #DirectoryTree AS d
INNER JOIN dirs ON d.ParentDirectory = dirs.id
)
SELECT * FROM dirs
-- Dir style ordering
ORDER BY container, isfile, subdirectory
COLLATE Persian_100_CI_AS;
December 9, 2014 at 4:11 am
My problem will be solved by using N before nvarchar field but I don't know how to use this N in CTE Query.
December 9, 2014 at 10:07 pm
hello.
In fact my problem is concatenate two Persian nvarchar.when I concatenate two nvarchar which this two nvarchar is Persian,my result is disarranged.please help me
December 10, 2014 at 1:46 am
Try to specify COLLATE for every char column in ORDER BY.
...
ORDER BY container COLLATE Persian_100_CI_AS, isfile, subdirectory COLLATE Persian_100_CI_AS;
Also note the difference between case sensitive and case insensitive collations (if it's applicaple in your case). For example look at results of
select * from (values (N'A', 1), (N'a', 2)) as t(ch, n)
order by ch collate Latin1_General_100_CS_AI, n
;
select * from (values (N'A', 1), (N'a', 2)) as t(ch, n)
order by ch collate Latin1_General_100_CI_AI, n
December 10, 2014 at 3:55 am
thank you so much dear ser.my problem was solved by your reply.thank you so much.
December 10, 2014 at 6:54 pm
serg-52 (12/8/2014)
This batch will show all objects (directories, files) and their full paths
--parameter
declare @myPath nvarchar(4000) = 'E:\ElectronicArchieve';
IF OBJECT_ID('tempdb..#DirectoryTree') IS NOT NULL
DROP TABLE #DirectoryTree;
CREATE TABLE #DirectoryTree (
id int IDENTITY(1,1)
,subdirectory nvarchar(512)
,depth int
,isfile bit
, ParentDirectory int
,flag tinyint default(0));
-- top level directory
INSERT #DirectoryTree (subdirectory,depth,isfile)
VALUES (@myPath,0,0);
-- all the rest under top level
INSERT #DirectoryTree (subdirectory,depth,isfile)
EXEC master.sys.xp_dirtree @myPath,0,1;
UPDATE #DirectoryTree
SET ParentDirectory = (
SELECT MAX(Id) FROM #DirectoryTree
WHERE Depth = d.Depth - 1 AND Id < d.Id)
FROM #DirectoryTree d;
-- SEE all with full paths
WITH dirs AS (
SELECT
Id,subdirectory,depth,isfile,ParentDirectory,flag
, CAST (null AS NVARCHAR(MAX)) AS container
, CAST([subdirectory] AS NVARCHAR(MAX)) AS dpath
FROM #DirectoryTree
WHERE ParentDirectory IS NULL
UNION ALL
SELECT
d.Id,d.subdirectory,d.depth,d.isfile,d.ParentDirectory,d.flag
, dpath as container
, dpath +'\'+d.[subdirectory]
FROM #DirectoryTree AS d
INNER JOIN dirs ON d.ParentDirectory = dirs.id
)
SELECT * FROM dirs
-- Dir style ordering
ORDER BY container, isfile, subdirectory
hope it helps.
Brilliantly simple! Well done! Added that one to my brief case.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 10, 2014 at 9:57 pm
Ah... Just a note. This DOES constitute a "Triangular Join", which can make things pretty slow unless you add some proper indexes. I ran it against C:\Windows and it took nearly 12 minutes without indexes. Adding the correct indexes brought the times down to about 12 seconds up through the UPDATE and a couple of other indexes will really help the rCTE, although I quit testing there. I quit because the following code did the whole thing in about 12 seconds. Of course, you might not be able to use xp_CmdShell where you work.
CREATE TABLE #MyHead
(
FullPath NVARCHAR(1000),
Directory AS SUBSTRING(FullPath, 1, LEN(FullPath)-CHARINDEX('\',REVERSE(FullPath))+1) PERSISTED,
[FileName] AS RIGHT(FullPath,CHARINDEX('\',REVERSE(FullPath))-1) PERSISTED
)
;
INSERT INTO #MyHead
EXEC xp_CmdShell 'DIR C:\Windows /s /b'
;
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply