Anytimescripts

Anytime Scripts

Functions/Views/Sprocs/etc

Anytime scripts are scripts that are run anytime they have changes. That means RH automatically detects new files and changs in files and runs when it finds changes.

How should I structure my scripts?

There are two methods to structure your scripts.

1. Drop/Create - this methodology will destroy permissions, but is easier to implement. If you are using this method, be aware that you will be dropping any item level permissions and will need to reapply them after you run.
2. Create If Not Exists / Alter - this method is better for making non-permission destructive changes. What does that look like?

/* This is the create if not exists part */
DECLARE @Schema varchar(20)
DECLARE @Name VarChar(100)
DECLARE @Type VarChar(20)
SET @Schema = 'ss' 
SET @Name = 'usp_GetAllThis'
SET @Type = 'PROCEDURE'

IF NOT EXISTS(SELECT * FROM sys.objects obj INNER JOIN sys.schemas sch on obj.schema_id = sch.schema_id WHERE obj.name = @Name AND sch.name = @Schema)
  BEGIN
	DECLARE @SQL varchar(1000)
	SET @SQL = 'CREATE ' + @Type + ' ' + @Schema + '.' + @Name + ' AS SELECT * FROM sys.objects'
	EXECUTE(@SQL)
  END
Print 'Updating ' + @Type + ' ' + @Schema + '.' + @Name
GO

/* Then all you do is set up alter like below */

ALTER PROCEDURE [ss].[usp_GetAllThis] 
/* your procedure guts here */

Related

See OneTimeScripts
See EveryTimeScripts