Sql Server 2008 Generate Script Process

Question

Post reply

Sql Server 2008 Generate Script Process

Chris Kitchen

Hall of Fame

Points: 3090
More actions
April 3, 2013 at 1:36 am

#270585

Hi,
I have a questions with regards to using Sql Server 2008 to automatically script out sql server objects.
If I right-click on a database and select "Tasks" and then "Generate Scripts.." under the "Choose Script Options" page if I select "Include If NOT EXISTS" and then script a stored procedure out to a New Query Window, the code is wrapped in an sp_executesql statements:
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Example]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'create procedure [dbo].[Example]
as
select top 1 [name] from sys.databases;
'
END
GO
Whereas if I just script the object out from SSMS manually it scripts it like this, which is exactly how I want it to be scripted:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [dbo].[Example]
as
select top 1 [name] from sys.databases;
GO
I need to be able to script thousands of SP's out of multiple databases into one file per object. I'm running Sql Server 2008, do you know if this behaviour also exists in Sql Server 2008 R2, and if so, a simple way to get the objects out, one per file without the "sp_executesql".
I could put something together in Powershell or using dynamic sql, but I was wondering if anyone had any better ideas, or if it was different in R2.
Thanks in advance,
Chris
www.sqlAssociates.co.uk

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

SQLCurious SSCrazy Points: 2339 More actions · Answer 1

Chris,

Attached is a document with screenshots describing how to script all SPs from a database into individual files.

- Rex

Lowell SSC Guru Points: 323504 More actions · Answer 2

also, the If Not Exists is a scripting option.

you can set it yourself, so that from whenenver you change the setting, it scripts the objects out as dynamic sql with the if not exists.

look here in in 2008 SSMS : Tools>>Options>>SQL Server Object Explorer>>Scripting

and toggle the flag "Include IF NOT EXISTS clause"

the screenshot is from SSMS 2005, but it's basically the same:

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Scott Coleman One Orange Chip Points: 27452 More actions · Answer 3

If you're picky about the scripting options, and you have to do this on multiple databases, and especially if this is going to be a recurring task, you probably should use PowerShell instead of Management Studio. I had a similar problem years ago, and while I don't remember specifics the issue was there was no one set of scripting options that gave me what I wanted for every object type. Once you figure out how to call the Script method with a ScriptOptions object that makes you happy, the rest is easy.

Chris Kitchen Hall of Fame Points: 3090 More actions · Answer 4

Hi Rex/Lowell,

Thanks for your replies. I am aware of how to script all objects in a database into individual files, the question is, I have to include the "IF NOT EXISTS" option and don't want the process to create the statement with dynamic sql, so has this changed for 2008 R2 or is it still the same?

I think I'm going to have to go down the Powershell route.

Thanks.

www.sqlAssociates.co.uk