@manhnguyenv

Welcome to my blog!

Script All Stored Procedures

September 5, 2018 10:13

Script All Stored Procedures (edit)

/*

How to script all stored procedures in a database
http://blogs.lessthandot.com/index.php/datamgmt/datadesign/how-to-script-all-stored-procedures-in-a/

Ctrl + Shift + F: Results to file

*/
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
GO

Categories

Recent posts