A stored procedure to call all SQL scripts in a folder

Question

I want to write a Stored Procedure so that I can execute some SQL scripts which I have saved in a particular folder. I have referred several articles, none of which give me a clear answer. Can anyone please give me any suggestions?

score 1 · Accepted Answer · edited May 15 '17 at 20:41

Obviously, the SQL-account will need access permissions to those files/folders.

DECLARE @FileContents  VARCHAR(MAX)

SELECT @FileContents=BulkColumn
FROM OPENROWSET(BULK'PathToYourFile.sql', SINGLE_BLOB) x;
EXECUTE(@FileContents)

To make it more 'dynamic':

DECLARE @path nvarchar(MAX)
DECLARE @FileContents varchar(MAX)


SET @path = N'C:\Users\username\Desktop\example.txt'
SET @path = REPLACE(@path, N'''', N'''''') 

DECLARE @sql nvarchar(MAX)
SET @sql = N'SELECT @FileContents=BulkColumn FROM OPENROWSET(BULK''' + @path + ''', SINGLE_BLOB) x;'

EXEC sp_executesql @sql, N'@FileContents varchar(MAX) OUTPUT', @FileContents=@FileContents OUTPUT;
EXECUTE(@FileContents);

Update You can list the files in a directory like this:

DECLARE @BasePath varchar(8000)
SET @BasePath = 'D:\temp'; -- List contents of this directory 


--Create a temp table to hold the results.
IF OBJECT_ID('tempdb..#DirectoryTree')IS NOT NULL 
    DROP TABLE #DirectoryTree; 

CREATE TABLE #DirectoryTree 
(
     id int IDENTITY(1,1)
    ,fullpath varchar(2000)
    ,subdirectory nvarchar(512)
    ,depth int
    ,isfile bit
);


INSERT #DirectoryTree (subdirectory, depth, isfile)
EXEC master.sys.xp_dirtree @BasePath, 1, 1;
-- SELECT subdirectory, * FROM #DirectoryTree 



-- Start Cursor 

DECLARE @fileName varchar(MAX) -- filename for backup 
DECLARE fileList CURSOR FOR ( SELECT subdirectory FROM #DirectoryTree WHERE isfile = 1 ) 

OPEN fileList 
FETCH NEXT FROM fileList INTO @fileName 

WHILE @@FETCH_STATUS = 0 
BEGIN 
    SET @fileName = @BasePath  + '\' + @fileName 
    PRINT @fileName 
    PRINT 'You can do something with the file here...'

    -- SET @fileContents = 'blabla'  get the file contents
    --EXECUTE('fileContents')


    FETCH NEXT FROM fileList INTO @fileName 
END   

CLOSE fileList 
DEALLOCATE fileList 

-- End Cursor 


IF OBJECT_ID('tempdb..#DirectoryTree')IS NOT NULL
    DROP TABLE #DirectoryTree;

I get an error saying `Cannot bulk load. The file "C:\Users\ssss\Desktop\abc.txt" does not exist.` Does **PathToYourFile.sql** mean the folder that I have saved the scripts or the path to a specific file? And does **example.txt** mean a text file that contains paths to the scripts? Sorry, I'm a bit confused. — Curiosity, May 15 '17 at 06:48
example.txt would be a file that contains sql code. Of course, you can also make a file that contains a list of paths. If you get this error, then either your path is wrong, your server does not run on your local computer, or the sql-user doesn't have access to this file. — Stefan Steiger, May 15 '17 at 07:07
Thanks a lot.. It worked.. If I want to run multiple SQL scripts at once using this, I just have to read the paths from another file or something similar to that right? Or is there any other way to run several scripts at once? Like, to give the folder path and then run all scripts in that location? — Curiosity, May 15 '17 at 07:26
@Curiousity: It's possible, as long as the sql-user has access to the path. See added code in post. — Stefan Steiger, May 15 '17 at 08:49

score 1 · Answer 2 · edited May 23 '17 at 12:26

You can use sqlcmd to get file names and to run scripts against your instance. You could do something like this:

DECLARE @path VARCHAR(128) = 'C:\SQLScripts\ '
       ,@cmd VARCHAR(1024)

CREATE TABLE dirList (
  line varchar(1000)
)

SET @cmd = 'dir /b ' + @path + '*.sql'

INSERT INTO dirList (line)
EXEC xp_cmdshell @cmd

DECLARE @actualFile VARCHAR(256)

DECLARE cur CURSOR FAST_FORWARD READ_ONLY FOR
SELECT * FROM dirList WHERE line IS NOT NULL;

OPEN cur
FETCH NEXT FROM cur INTO @actualFile

WHILE @@FETCH_STATUS = 0
BEGIN

SET @cmd = 'sqlcmd -S <ComputerName>\<InstanceName> -i ' + @path + @actualFile
EXEC xp_cmdshell @cmd

FETCH NEXT FROM cur INTO @actualFile

END

CLOSE cur
DEALLOCATE cur

DROP TABLE dirList

Notes:

xp_dirtree is the better option than xp_cmdshell. But yes, that works. — Stefan Steiger, May 15 '17 at 08:50

A stored procedure to call all SQL scripts in a folder

2 Answers2