Thursday, April 14, 2011

SQL to generate Script for All stored procedures in a Database

Hi,
I had a requirment to create a local copy of a MS SQL Database hosted on remote environment(Godaddy Hosting). With the help for Import Data option in SQL Server Managment studio, I was able to replicate both the Tables & their respective Data. Since there no option to import Stored procedures. I was looking in google to generate SQL script for all the stored procedures from a Database.


List All Stored Procedure in Database: select * from sys.procedures

Fortunate to find this post: http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/how-to-script-all-stored-procedures-in-a



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' +
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




Hope it Helps






4Z