Directory Listing in SSMS

Get a complete directory listing of the contents of folders and subfolders with the below script. This is useful if you don’t have direct access to the server operating system where SQL Server is installed, and you need a list of files. For instance, I regularly use this to list all the data files, log files, and backup files the server can see. It is also very useful for verifying if the SQL Server has access to a particular folder structure.

Watch that mallet!

Watch that mallet!

One thing to note, you’ll need to enable the xp_cmdshell extended stored procedure, via the sp_configure system stored procedure, before running this script. If you’re interested in determining free space on your SQL Server’s disk, check out our post, drives, with capacity and free space.

You may notice the code above prefixes the path with \\?\ – this tells Windows that we want to use long-file-name API. While this is not strictly necessary, you may run into a situation where you have a path longer than the 260 characters allowed by SQL Server. This code can help you verify that since it will display very, very long paths. I suggest storing filenames in SQL Server using a maximum of 260 characters, since SQL Server can only ever directly access paths/filenames no longer than 260 characters. See this answer I wrote on StackOverflow for details about that.

The /ogn option on the DIR command above produces output sorted with directories listed first, then files, in alphabetic order.

Let me know in the comments below if you found this useful, or if you have any questions about how to use it, or if you find a problem with it.

Also, don’t forget to check out the rest of our tools.