question

knt1N-9820 avatar image
0 Votes"
knt1N-9820 asked knt1N-9820 edited

How management studio retrieves a list of file names.

I'm using SQL Server 2017 Standard.

I want to realize is to get a list of file names in folders on Windows and save them to a table, by run SQL in Management Studio.
For that, I need to get a list of file names in folders on Windows, by run SQL in Management Studio.

I know that with "xp_cmdshell", I can make this happen.
However, I believe there are concerns about "xp_cmdshell", it is better not to actively use it on the reason of security risk.

Therefore, I want to know if there is no other way.
I'd appreciate it if someone could tell me how to solve this problem.

sql-server-generalsql-server-transact-sql
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Does it have to be done using SSMS and T-SQL? What is the purpose of getting these file names into SQL Server? I would create a Powershell script to get the list of file names and Invoke-SqlCmd to insert the data into a table.

0 Votes 0 ·
knt1N-9820 avatar image knt1N-9820 JeffreyWilliams-3310 ·

It doesn't use loops, And if I can insert multiple data at once as follows, I think that the method of using PowerShell is also good.

INSERT [Table] SELECT [FileName] FROM [FileNameList];

The reason for using SSMS is that the script must be saved in a Maintenance Plan and have it run regularly.

I don't think looping is appropriate because this job requires a lot of data insertion in a single run.
If you know the right way to insert a lot of data without looping using the Power Shell, it would be greatly helpful if you could tell me.

0 Votes 0 ·
Cathyji-msft avatar image
0 Votes"
Cathyji-msft answered knt1N-9820 commented

Hi @knt1N-9820,

You can using xp_DirTree get the list of folders files in a SQL table.

    exec xp_DirTree directory ,  depth , file 

1.directory – This is the directory path of any files .
2.depth – It specify the subfolder levels to display. The default of 0 will display all subfolders.
3.file – This will either display files as well as each folder. The default of 0 will not display any files.

Refer to the blog SQL Query to get the list of files in a folder in SQL to get the detail example information.


If the response is helpful, please click "Accept Answer" and upvote it, as this could help other community members looking for similar thread.



· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hello.
Thank you for your answer.

I tried the methods I was taught in my environment and made sure it worked the way I wanted it to.

Thank you again for your advice.

0 Votes 0 ·
Viorel-1 avatar image
0 Votes"
Viorel-1 answered knt1N-9820 edited

Consider dm_os_enumerate_filesystem (https://docs.microsoft.com/en-us/answers/questions/447592/), for example:

 select * 
 from sys.dm_os_enumerate_filesystem('C:\MyFolder', '*.*')


· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Note that this function, too, is a potential security risk. By default, public has access to this function which access the file system under the context of the SQL Server account. As a consequence, it may expose information that should not be exposed to anyone.

This KB article discusses how it can be disabled: https://support.microsoft.com/en-us/topic/kb4046638-add-the-ability-to-disable-or-enable-a-few-new-dmvs-and-dmfs-introduced-in-sql-server-2017-05d709c5-4522-3168-2cb1-f755fddff9e7

0 Votes 0 ·

Hello.
Thank you for writing a comment.

I understood that the disabling feature has been added, as you are talking about.

I was aware that "xp_cmdshell" was a problem that SQL Server could manipulate all commands, until now.
But, I think this is also a concern by also about the possibility of information being made public that should not be made public.

However, since there are several cases where SQL Server wants to refer to the file system, it is also difficult to disable them all.
This time, what I want to think about is a configuration that SQL Server can refer to for the file system.

Thank you again for your advice.

0 Votes 0 ·

Hello.
Thank you for your answer.

I tried the methods I was taught in my environment and made sure it worked the way I wanted it to.

Thank you again for your advice.

0 Votes 0 ·