CODEWITHSUNDEEP
sql-serverstored-proceduresssms
Curiosity
Curiosity

Reputation: 1931

A stored procedure to call all SQL scripts in a folder

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?

Upvotes: 0

Views: 6463

Answers (2)

Stefan Steiger
Stefan Steiger

Reputation: 82186

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;

Upvotes: 1

Paweł Tajs
Paweł Tajs

Reputation: 482

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:

Upvotes: 1

Related Questions