BISQL # 60 – How to Take Fast Script only for Stored Procedures from Data Base
Hi friends
Today when I am working on development environment suddenly I got one request from my one of colleague I need to have Script only stored Procedure !!
We have other interface option for SSMS but still I develop this script for quick patch deployment and Rapid Development.
Those who do not have time for research just copy paste script which @ end of this article and use it !!
Following Are advantage of this script :
1.Use blindly this script for entire stored procedure scripting without thinking 
2.Script is very simple to use
3.No need of interface
4.No input parameter for script
5.By this scripting SP’s are mechanical work
6.We can take use this for single SP scripting by some modification .
Features of SQL script :
1.If stored procedure is already exists then also this will drop and create as it take cares of everything
2.We just have select Database for which we have take script for SP’s
3.Once script is execute just copy ,paste and execute on required destination .
Script to Generate schema for Stored Procedure :
Just copy this and execute it will create everything for you
SET NOCOUNT ON DECLARE @tempTable TABLE (ID int identity(1,1), Sproc varchar(max)) INSERT INTO @tempTable (Sproc) SELECT 'if object_id([' + 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 @index int, @maxCount int DECLARE @allsprocs varchar(max) SELECT @maxCount = max(ID) from @tempTable SET @index = 1 WHILE @index <= @maxCount BEGIN SELECT @allsprocs = Sproc FROM @tempTable WHERE id = @index WHILE @allsprocs <> '' BEGIN PRINT left(@allsprocs,8000) SET @allsprocs = substring(@allsprocs, 8001, len(@allsprocs)) END SET @index = @index + 1 END
Hope this helps !!
Thanks for visiting my blog !!
If you really like reading my blog and understood at lest few thing then please don’t forget to subscribe my blog .
If you wan daily link and analysis or interesting link go to following website which will give @ your inbox please subscribe our following link resource blog
Where todays links are
SQL Server Mentalist 