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

image

2.We just have select Database for which we have take script for SP’s

image

3.Once script is execute just copy ,paste and execute on required destination .

image 

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

Link Resource Website

About these ads
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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 1,120 other followers

%d bloggers like this: