scheduled execution of all TSQL scripts in a folder

Question

I have a bunch of TSQL scripts that need to be executed on a daily basis. I know I can use Job Agent to execute them but that requires me to change the actual job.

What I would like to do is create a job that simply says:

execute all TSQL-scripts in <some folder>

A bonus would be if a filter could be used based on the filename of the script: So that one job would execute all the files whose name start with a 'd', another job would execute all those with a 'w' in the name.

Can this be done? How can this be done?

I read some things that spoke of using the Windows-scheduler to run the SQLCMD-utility. I'd rather have the SQL Server do the scheduling and executing. Is Powershell the way to go? If so, where and how to start? (Never had to use it so never gave it much attention :/)

Thanks for thinking with me!

Henro

Answer 1

João, thanks for your help. Using your code I produced this: set dateformat dmy

DECLARE @scripts varchar(100) = 'C:\MYSCRIPTS\'     -- folder with scripts
DECLARE @project varchar(100) = 'PROJECTX'  -- specific folder
DECLARE @Identifier varchar(1) = 'D' -- All files of which the name starts with a 'T'
DECLARE @files  varchar(100) = @scripts + @project + '\' + @Identifier + '*.sql'

CREATE TABLE #tbl1 (Name varchar(400))
DECLARE @cmd varchar(100) = 'dir ' + @files 
INSERT #tbl1 EXECUTE master.dbo.xp_cmdshell @command_string = @cmd
DELETE FROM #tbl1 WHERE  ISDATE(SUBSTRING(Name,1,10))  = 0
UPDATE #tbl1 SET Name = SUBSTRING(Name,37, 100)

CREATE TABLE #tbl2 (Counter smallint Primary Key IDENTITY(1,1), Name varchar(400))
INSERT INTO   #tbl2 (Name)
Select  @scripts + @project + '\' + Name from #tbl1

DECLARE @i int
DECLARE @NumRows int
DECLARE @File2BExecuted varchar(100)

SET @i = 1
SET @NumRows = (SELECT COUNT(*) FROM #tbl2)
IF @NumRows > 0
    WHILE  (@i <= (SELECT MAX(Counter) FROM #tbl2))
    BEGIN
    SELECT @File2BExecuted = Name FROM #tbl2 WHERE Counter = @i
    DECLARE @script varchar(100) = 'sqlcmd -S ' + @@SERVERNAME + ' -i ' + @File2BExecuted
    EXECUTE master.dbo.xp_cmdshell @command_string = @script

    SET @i = @i + 1
    END

drop table #tbl1
drop table #tbl2