SQL Server xp_cmdshell

Question

Is there any other way to get a list of file names via T-SQL other than

INSERT INTO @backups (file name) EXEC master.sys.xp_cmdshell 'DIR / bc: \ some sql backup folder in it

I am trying to get a list of sql backup files from a recovery folder, and I DO NOT want to use xp_cmdshell for obvious security reasons.

Answer 1

Alternative to xp_cmdshell, I went below:

, . ( SQL ), . #, (, FooA.txn, FooB.txn, FooC.txn). , , . temp, .

# xp_cmdshell, .

Answer 2

, , , .

http://msdn.microsoft.com/en-us/library/ms188062.aspx

.

Answer 3

, :

• SQL2005 2008, CLR . , , , , , , , , , SQL, .
• , , MSDB. , msdb.dbo.backupmediafamily.physical_device_name, , , , , : RESTORE FILELISTONLY disk='FULL_PATH_TO_YOUR_FILE'. , . T-SQL , @@ .
• , script, Windows, SQL Server, . , . , 15 , , script, SQL Server. Perl, Ruby VBScript. , . , , , 100% SQL Server, script.

Answer 4

I understand that this thread is 5 years old, but I thought I would send another non-xpCmdShell alternative not related to the CLR. Details are given in the following code, and this is pretty simple stuff.

--===== Define the path and populate it.
     -- This could be a parameter in a proc
DECLARE @pPath VARCHAR(512);
 SELECT @pPath = 'C:\Temp';

--===== Create a table to store the directory information in
 CREATE TABLE #DIR
        (
        RowNum     INT IDENTITY(1,1),
        ObjectName VARCHAR(512),
        Depth      TINYINT,
        IsFile     BIT,
        Extension AS RIGHT(ObjectName,CHARINDEX('.',REVERSE(ObjectName))) PERSISTED
        )
;
--===== Get the directory information and remember it
 INSERT INTO #DIR
        (ObjectName,Depth,IsFile)
   EXEC xp_DirTree 'C:\Temp',1,1
;
--===== Now do whatever it is you need to do with it
 SELECT * FROM #DIR;