Home > Query, SQL > BISQL # 60 – How to Take Fast Script only for Stored Procedures from Data Base

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 Smile

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

      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
            SELECT @allsprocs = Sproc FROM @tempTable WHERE id = @index
            WHILE @allsprocs <> ''
                  PRINT left(@allsprocs,8000)
                  SET @allsprocs = substring(@allsprocs, 8001, len(@allsprocs))
            SET @index = @index + 1


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

Link Resource Website

Categories: Query, SQL
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: