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!
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 |
/* Displays a directory listing, simply set the @folder_filter to the desired path, including DOS wildcards, if desired. Hannah Vernon 2022-05-13 */ SET NOCOUNT ON; DECLARE @folder_filter varchar(2000) = 'F:\SQL_Server_Log_Files\'; DECLARE @bare_format bit = 0; DECLARE @include_subfolders bit = 1; DECLARE @cmd varchar(2500); DECLARE @line varchar(2500); DECLARE @output TABLE ( [rowid] int NOT NULL IDENTITY(1,1) PRIMARY KEY CLUSTERED , [output] varchar(1000) NULL ); /* Construct the DOS DIR cmd. The \\?\ indicates we want support for long-file-names up to approx 32768 characters. SQL Server doesn't support paths longer than 260 characters, however it's possible you may want to look at long paths via this script. */ SET @cmd = 'DIR \\?\"' + @folder_filter + '" /ogn ' + CASE WHEN @include_subfolders = 1 THEN N' /s' ELSE N'' END + CASE WHEN @bare_format = 1 THEN N' /b' ELSE N'' END; INSERT INTO @output ([output]) EXEC sys.xp_cmdshell @cmd; DECLARE cur CURSOR LOCAL READ_ONLY STATIC FORWARD_ONLY FOR SELECT o.[output] FROM @output o ORDER BY o.[rowid]; OPEN cur; FETCH NEXT FROM cur INTO @line; WHILE @@FETCH_STATUS = 0 BEGIN SET @line = REPLACE(@line, N'\\?\', N'') PRINT @line; FETCH NEXT FROM cur INTO @line; END CLOSE cur; DEALLOCATE cur; |
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.