How can we help you today? How can we help you today?

detecting create vs alter differences

We are using SQLCompare to compare against a database and scripts folder.

In our scripts for for objects such as Stored procedures, views, functions, we currently use the "IF NOT EXIST... create object ... GO ... alter object" structure to create/alter the object.

This is different than the standard approach of IF EXISTS DROP... CREATE. We don't do the latter approach because we can't risk accidently losing permissions on this object (from the DROP statement) when deploying this to production.

However, SQL Compare will always complain about a difference in this object due to the difference in "CREATE" versus "ALTER".

Any suggestions on how to get SQL Compare to NOT report a difference?
Thank you

Ray

EXAMPLE:

IF NOT EXISTS (
SELECT *
FROM dbo.sysobjects
WHERE id = OBJECT_ID(N'ufn_MyFunc') AND xtype IN (N'P', N'V', N'FN', N'TF', N'TR'))
EXEC('CREATE FUNCTION ufn_MyFunc() RETURNS varchar(5) AS BEGIN return ''empty'' END')
GO

ALTER FUNCTION ufn_MyFunc
... etc...
rchan
0

Comments

2 comments

Sort by
  • chris.buckingham
    I'm sorry to say that there isn't a method to pass a pragma or change this part of the scripting at present. However I will pass your request to the developers for assessment for possible inclusion.
    chris.buckingham
    0
  • jamal
    Yes, we are also thinking of using this method to store object definitions in source control. We'd like to use sql compare to compare our source control scripts to what's on a given server, however, sql compare marks the objects as different because what it gets from the server has CREATE and what it gets from source control says ALTER. That is the only difference. Is there an option to ignore these differences?
    jamal
    0

Add comment

Please sign in to leave a comment.