Generate Script Wizard in SQL 2005/2008

Posted by Vuyiswamb under Sql Server on 6/19/2010 | Views : 3400 | Status : [Member] [MVP] [Administrator] | Replies : 3
Good Day All

maybe i am missing something here. I have Database A and Database B. These two database have a Stored procedure called "sp_OOPS" and in all the databases the Stored Procedure is Difference. Without any any third party software , is there a way to use Generate Script Wizard to Sync Database B with Database A.

basically when i use this wizard, it can do the if exists and all other nice sql futures, but if the sp definations are not the same it does not alter. Why i am trying to understand , is does the Wizard do that ?

Thanks

Thank you for posting at Dotnetfunda
[Administrator]



Responses

Posted by: Deeraj on: 6/23/2010 [Member] Starter

Up
0
Down
Hi Vuyiswamb,

Could you please elaborate the question with a few code snippets?

Thanks,
Dheeraj.

Vuyiswamb, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Vuyiswamb on: 6/23/2010 [Member] [MVP] [Administrator] NotApplicable

Up
0
Down
Good Day Deeraj

Well the question does not need any snipet example, but let me try to give you one.

Lets say i have

DatabaseA
================

Create proc usp_MyStoredprocedure
as
select 'Hello World'


and i have another Database

DatabaseB
===========

Create proc usp_MyStoredprocedure
as
select 'Oops i am Different'


now as you can see all these databases have the Storedprocedure named usp_MyStoredprocedure , Now i want to Script the DatabaseA and it must also Script the Alter if the Stored procedures in DatabaseA exist in DatabaseB , else if those StoredProcedure does not Exists then it should create them.

Now my question is, the SQL Generate Script Wizard that comes with SQl does the Exist , which is the Create of an object and it does the drop , but the Problem here it does not do the Alter as far as i have checked, but what i was saying is that i might be missing a setting in the Wizard that does that. So maybe you can shed the light there.

Hope you understand

Thanks



Thank you for posting at Dotnetfunda
[Administrator]

Vuyiswamb, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Deeraj on: 6/24/2010 [Member] Starter

Up
0
Down

---Your expectation:
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_MyStoredprocedure]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
Create proc [dbo].[usp_MyStoredprocedure]
as
Begin
select ''Hello World''
End'
END
ELSE
EXEC dbo.sp_executesql @statement = N'
Alter proc [dbo].[usp_MyStoredprocedure]
as
Begin
select ''Hello World''
End'
GO

--SQL Generate Script wizard does this:
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_MyStoredprocedure]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[usp_MyStoredprocedure]
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_MyStoredprocedure]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
Create proc [dbo].[usp_MyStoredprocedure]
as
Begin
select ''Hello World''
End'
END


Summary of the above variants:

1. If SP doesnot exist Create SP Else Alter SP
2. Drop SP, Create SP

Where
#1 is your expectation
#2 is SQL Server Wizard's implementation

Conclusion:
#1 and #2 are the same, the only overhead is dropping the SP in step #2.

Hope that helps!

Vuyiswamb, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response