Generate Script Wizard in SQL 2005/2008

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

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

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]

Reply | Reply with Attachment

Alert Moderator

Responses

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

0	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

0	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

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

0	---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


---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

Latest Posts