How to Script Out Stored Procedures to Files

How to script out stored procedures to files?

Stored procedures aren't stored as files, they're stored as metadata and exposed to us peons (thanks Michael for the reminder about sysschobjs) in the catalog views sys.objects, sys.procedures, sys.sql_modules, etc. For an individual stored procedure, you can query the definition directly using these views (most importantly sys.sql_modules.definition) or using the OBJECT_DEFINITION() function as Nicholas pointed out (though his description of syscomments is not entirely accurate).

To extract all stored procedures to a single file, one option would be to open Object Explorer, expand your server > databases > your database > programmability and highlight the stored procedures node. Then hit F7 (View > Object Explorer Details). On the right-hand side, select all of the procedures you want, then right-click, script stored procedure as > create to > file. This will produce a single file with all of the procedures you've selected. If you want a single file for each procedure, you could use this method by only selecting one procedure at a time, but that could be tedious. You could also use this method to script all accounting-related procedures to one file, all finance-related procedures to another file, etc.

An easier way to generate exactly one file per stored procedure would be to use the Generate Scripts wizard - again, starting from Object Explorer - right-click your database and choose Tasks > Generate scripts. Choose Select specific database objects and check the top-level Stored Procedures box. Click Next. For output choose Save scripts to a specific location, Save to file, and Single file per object.

These steps may be slightly different depending on your version of SSMS.

Export multiple stored procedures to text files

Use the Generate Scripts tool in SSMS:

1. Right Click Database in Object Explorer.
2. Tasks -> Generate Scripts.
3. If given the "tutorial" click Next.
4. Select "Select specific database objects" and tick "Stored Procedures". Click Next.
5. Choose export method. Likely here you want "Save as script file" with "one script file per object" selected. Ensure you choose the export location.
6. Click Next and Finish buttons as required.

How to generate script of each stored procedure as separate .SQL file in Microsoft SQL Server?

In Set Scripting Options while Generate Scripts, use setting like this:

How to store output of a SQL Server stored procedure in a .txt file

As the comments and other answers indicate, this is not usually a good idea. But here's how to do it anyway, assuming you're a sysadmin on SQL Server. :)

-- 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 OR ALTER PROCEDURE [dbo].[usp_printresulttofile]
AS
BEGIN
    DECLARE @var NVARCHAR(MAX) = ''
    SET @var = 'print this data in txt file'
    PRINT 'Data is : ' + @var   

    declare @fn varchar(200) = 'c:\temp\out.txt';

    declare @cmd varchar(8000) = concat('echo ', @var, ' > "', @fn, '"');

    print @cmd 
    exec xp_cmdshell @cmd,  no_output

    set @cmd  = concat('type "', @fn, '"');

    print @cmd 
    exec xp_cmdshell @cmd;

END
go
EXEC [dbo].[usp_printresulttofile]

export stored procedures through SQL script

How about using INFORMATION_SCHEMA.Routines ?

DECLARE MY_CURSOR Cursor
FOR
SELECT r.Routine_Definition
FROM INFORMATION_SCHEMA.Routines r 
OPEN MY_CURSOR
    DECLARE @sproc VARCHAR(MAX) 
    FETCH NEXT FROM MY_CURSOR INTO @sproc
    WHILE (@@FETCH_STATUS <> -1)
    BEGIN
        IF (@@FETCH_STATUS <> -2)
        PRINT @sproc
        FETCH NEXT FROM MY_CURSOR INTO @sproc
    END
CLOSE MY_CURSOR
DEALLOCATE MY_CURSOR
GO

---

EDIT

It sounds like you might want something like this to include the LAST_ALTERED date and Definition in a result set.

SELECT
    r.LAST_ALTERED,
    r.ROUTINE_NAME,
    r.Routine_Definition
FROM INFORMATION_SCHEMA.Routines r 

A stored procedure to call all SQL scripts in a folder

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; 

t-sql stored procedure create scripts

You can right-click on the database in the Object Explorer and do a Task > Generate Scripts.

That allows you to pick a whole bunch of objects to be scripted (e.g. tables, views, stored procs) and you can store those into a single big SQL file, or one SQL file per object. Works really quite well!

Update: if you want to do this in the SQL Server Management Studio app, you can use this SQL script to find the stored procs and their definitions - you cannot however have SQL Server Mgmt Studio write out the files to disk, that doesn't work - but you can copy the results into e.g. Excel.

SELECT 
    pr.name ,
    pr.type_desc ,
    pr.create_date ,
    mod.definition
FROM sys.procedures pr
INNER JOIN sys.sql_modules mod ON pr.object_id = mod.object_id
WHERE pr.Is_MS_Shipped = 0

---

Related Topics