Microsoft – SQLServer – Script Database Objects (Stored Procedure) – “Annoying” sp_executesql

Microsoft – SQLServer – Script Database Objects – “Annoying” sp_executesql

As a DBA one often has a need to script out Database Objects.

You need to copy objects from one System to another, you need to back them up prior to over-writing them, etc.

Whatever your need might be, script them out.

Microsoft SQL Server Management Studio offers a couple of paths for scripting out objects.

Option – 1 – Script Object

  • First, launch Microsoft SQL Server Management Studio
  • Select the object and right click on the selection
  • From the drop-down menu, Select “Script <object> as”

Option – 2 – Generate Scripts

  • First, launch Microsoft SQL Server Management Studio
  • Select the database and right click on your selection
  • From the drop-down menu Select “Tasks” / “Generate Scripts…”

When you choose to generate Scripts via the Database \ “Generate Scripts” path, you

sometimes get a code that looks like:

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_ProcessTask]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'

CREATE PROCEDURE [dbo].[usp_ProcessTask]
AS
BEGIN
	SET NOCOUNT ON;

END
'

END

What comes out is not just a clean Stored Procedure script, but:

  • A quick check to see whether the object exists
  • If the Object does not exist, a call out to dbo.sp_executesql to go create the object
  • Sending parameters out to sp_executesql is not the easiest thing in the World.  Some of your text needs to be double-quoted

To make the resulting code easier to read, MS could simply have Googled and found a solution SQL Developers have started using:

It reads:

     set noexec off
     go

     if object_id('dbo.usp_ProcessTask') is not null
     begin

        set noexec on

     end

     create procedure dbo.usp_ProcessTask
     as
     begin

            ....
            ....

     end

     go

     set noexec off
     go

What choices are available:

  • Uncheck the “If exists” choice; which is the default choice

Leave a Reply

Please log in using one of these methods to post your comment:

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