SQL Get Files Under folder

--go
--create function fn_split(@str nvarchar(4000), @delimiter nvarchar(1))
--returns @tempData table (slice nvarchar(4000))
--as
--begin

--declare @index int
--declare @slice nvarchar(4000)

--if LEN(@str)<1 or @str is null return

--set @index=charindex(@delimiter,@str)

--while(@index<>0)
--begin
--set @slice=substring(@str,1,@index-1)
--insert @tempData (slice) values (@slice)
--set @str=substring(@str,@index+1,len(@str))
--set @index=charindex(@delimiter,@str)
--end

--if len(@str)>0 insert @tempData(slice) values (@str)

--return
--end

--go

 

--go
--create function fn_getLast(@str nvarchar(4000),@delimiter nvarchar(1))
--returns nvarchar(4000)
--as
--begin
--declare @r nvarchar(4000)
--select @r =slice from dbo.fn_split(@str,@delimiter)
--return @r
--end
--go

 

 

--GO
-- To allow advanced options to be changed.
--EXEC sp_configure 'show advanced options', 1
--GO
---- To update the currently configured value for advanced options.
--RECONFIGURE
--GO
---- To enable the feature.
--EXEC sp_configure 'xp_cmdshell', 1
--GO
---- To update the currently configured value for this feature.
--RECONFIGURE
--GO

create table #filePath(FilePath varchar(300))
declare @folder varchar(300)
declare @return int
set @folder='dir your folder'
insert #filePath exec @return =master.dbo.xp_cmdshell @folder
select dbo.fn_getLast(FilePath ,' ')
from #filePath
where FilePath like '%.sql'
drop table #filePath

转载于:https://www.cnblogs.com/mjgb/archive/2012/04/27/2474208.html