How to load all stored procedures from a specific database

Question

I need to load all stored procedures from a specific database.

There are about 130 stored procedures, and I can do it manually, for example, saving each as a file.

But is there any automatic option to download everything?

Answer 1

1) Open SQL Server Management Studio 2) Select your database in the Object Explorer 3) Right-click> Tasks> Generate Scripts

4) Select only the stored procedures that should be written by the script

5) After the wizard through the steps; on the next screen, select the Single file per object option and determine the directory where to place these files:

With these parameters, you get one file in a stored procedure , stored in a directory of your choice.

Answer 2

You can do this in a management studio. Right-click the desired database and select tasks → Generate scripts → go through the wizard. Then you can specify only stored procedures, etc.

You can also use the script as follows:

 SET NOCOUNT ON DECLARE @Test TABLE (Id INT IDENTITY(1,1), Code VARCHAR(MAX)) INSERT INTO @Test (Code) SELECT 'IF object_ID(N''[' + schema_name(schema_id) + '].[' + Name + ']'') IS NOT NULL DROP PROCEDURE ['+ schema_name(schema_id) +' ].[' + Name + ']' + CHAR(13) + CHAR(10) + 'GO' + CHAR(13) +CHAR(10) + OBJECT_DEFINITION(OBJECT_ID) + CHAR(13) +CHAR(10) + 'GO' + CHAR(13) + CHAR(10) FROM sys.procedures WHERE is_ms_shipped = 0 DECLARE @lnCurrent INT, @lnMax INT DECLARE @LongName VARCHAR(MAX) SELECT @lnMax = MAX(Id) FROM @Test SET @lnCurrent = 1 WHILE @lnCurrent <= @lnMax BEGIN SELECT @LongName = Code FROM @Test WHERE Id = @lnCurrent WHILE @LongName <> '' BEGIN PRINT LEFT(@LongName,8000) SET @LongName = SUBSTRING(@LongName, 8001, LEN(@LongName)) END SET @lnCurrent = @lnCurrent + 1 END 

You can also shift + click to select all stored procedures, and then you can right-click and script them into a file.

Answer 3

You can also use DB pro (Visual Studio tools for the database). For more information check this out - https://www.mssqltips.com/sqlservertip/2971/creating-a-visual-studio-database-project-for-an-existing-sql-server-database/

Edit: Updated outdated link.