June 17, 2008 at 8:04 am
Hi All,
In Generate Script, is it possible to create like below stmts.
I don't want sp_executesql stmts in the generate Script.
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[spTest]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[spTest]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[spTest]
(
@test-2 nvarchar(128)
)
----
----
----
June 17, 2008 at 8:13 am
I created the following just by using the "Script Object as..." right-click functions:
/****** Object: StoredProcedure [dbo].[LookupTest1] Script Date: 06/17/2008 10:10:10 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[LookupTest1]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[LookupTest1]
go
/****** Object: StoredProcedure [dbo].[LookupTest1] Script Date: 06/17/2008 10:10:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE proc [dbo].[LookupTest1]
(@ID_in int,
@ID int = 1)
as
select @id = @id_in
select caseid
from dbo.casedetails
where caseid = @id
Is that the kind of thing you're looking for? If so, script the drop command to a new window, the create command to the clipboard, then paste it. Very easy.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 17, 2008 at 10:46 pm
Hi GSquared,
Thanks GSquared for your quick answer.
When i have more than 500 objects then it will be very hard to paste it rt? If so, in that situation what shall i do for the same. 🙂
June 18, 2008 at 7:17 am
Right-click the database in Management Studio, select Tasks -> Generate Scripts...
It will walk you through a wizard where you pick which objects to script, whether or not to include the drop commands for them, etc. You can select options like "all stored procedures".
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 18, 2008 at 7:38 am
hi GSquared,
in that i selected drop option, if i use this i will not get the IF Exists stmts like this.
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_Test]') AND type in (N'P', N'PC'))
How can i get this.
Example:
-------
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_Test]') AND type in (N'P', N'PC'))
DROP PROCEDURE sp_Test
Create Procedure sp_Test.
June 18, 2008 at 7:49 am
I believe there's a setting in the scripting options for that.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 18, 2008 at 7:53 am
Hi GSquared,
Yes GSquared. i have to choose Append to file option in that.
at last i got it.
Thanks for your kind help. Thanks man :):):)
---
June 18, 2008 at 8:19 am
You're welcome.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply