FW: TSQL - Function to List files and folders inside directory

[Pramod Jamadagni]

fyi

 

From: Chandan Kumar
Sent: 19 October 2012 12:26
To: Pramod Jamadagni
Subject: RE: TSQL - Function to List files and folders inside directory

 

EXEC Master.dbo.xp_DirTree 'c:\',1,1

 

From: Pramod Jamadagni
Sent: 19 October 2012 12:21
To: Chandan Kumar
Subject: FW: TSQL - Function to List files and folders inside directory

 

fyi

 

From: Pramod Jamadagni
Sent: 07 September 2012 01:35
To: Pramod Jamadagni
Subject: TSQL - Function to List files and folders inside directory

 

 

Hi,

This is the general function to list files and folders

returns a table representing all the items in a folder. It takes as parameter the path to the folder. It does not take wildcards in the same way as a DIR command. Instead, you would be expected to filter the results of the function using SQL commands

Notice that the size of the item (e.g. file) is not returned by this function.

 

This function uses the Windows Shell COM object via OLE automation. It opens a folder and iterates though the items listing their relevant properties. You can use the SHELL object to do all manner of things such as printing, copying, and moving filesystem objects, accessing the registry and so on. Powerful medicine.

 

--e.g.

--list all subdirectories directories beginning with M from "c:\program files"

SELECT [path] FROM dbo.dir('c:\program files')

WHERE name LIKE 'm%’ AND IsFolder =1

SELECT * FROM dbo.dir('C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG')

 

 

Thanks,

Pramod.