Generate Script With If EXIST DROP But Do Not Include If Not Exists In CREATE In SQL Server

Question

I want to generate the script using Tasks | Generate Scripts with IF Exist Drop Stored Procedure.

But I don't want to include IF NOT EXISTS - Create Stored Procedure while generating Script then what should I do?

Current

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SPNAME]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[SPNAME]
GO

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

Required

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SPNAME]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[SPNAME]
GO

CREATE PROCEDURE [dbo].[SPNAME] AS 

Can anyone please help me!

Solution 1

There is no direct option available to do this thing. Follow below step.

1. Generate DROP scripts for all objects.

   • Include If NOT Exists = True
   • Script DROP and CREATE = Script DROP

2. Generate CREATE scripts for all objects.

   • Include If NOT Exists = False
   • Script DROP and CREATE = Script CREATE
   • Append to File = True

2nd step will append contents of drops scripts which is generated in 1st step.

If you want to merge all files in one file then use bellow command

for %f in (*.sql) do type "%f" >> c:\Test\output.sql

Query Options

Drop

Create

Solution 2

You can enable the "Check for object existence" option to true and generate the drop create script. It should work for 2017 or Azure SQL DB

Solution 3

With SQL Server 17 it's

Script DROP and CREATE

See: