TSQL - Function to List files and folders inside directory

[Pramod Jamadagni]

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.