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!

DROP -> CREATE could lead to losing information about permissions. CREATE -> ALTER is safer. — Lukasz Szozda
– Lukasz Szozda, Commented Jun 23, 2017 at 8:46
could you please share any link to get the knowledge for this, it would be really appreciated. thanks @lad2025 — Pedram
– Pedram, Commented Jun 23, 2017 at 9:02

Rikin Patel · Accepted Answer · 2017-06-21 12:45:06Z

5

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

Generate DROP scripts for all objects.
- Include If NOT Exists = True
- Script DROP and CREATE = Script DROP
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

edited Jun 21, 2017 at 12:45

answered Jun 21, 2017 at 12:20

Rikin Patel

9,4738 gold badges74 silver badges80 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Rupesh Kumar · Accepted Answer · 2019-06-28 06:48:02Z

3

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

answered Jun 28, 2019 at 6:48

Rupesh Kumar

1362 bronze badges

Comments

krul · Accepted Answer · 2018-08-24 11:38:11Z

1

With SQL Server 17 it's

Script DROP and CREATE

See:

answered Aug 24, 2018 at 11:38

krul

2,2794 gold badges31 silver badges52 bronze badges

Collectives™ on Stack Overflow

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

3 Answers 3

Query Options

Drop

Create

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Query Options

Drop

Create

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related