Anytimescripts

Anytime Scripts

Folders

• AlterDatabase - New in v0.8.5!
• RunFirstAfterUpdate
• Functions
• Views
• Sprocs
• Indexes - New in v0.8.5!
• RunAfterOtherAnyTimeScripts

Anytime scripts are scripts that are run anytime they have changes. That means RH automatically detects new files and changes 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. What does it look like with SQL Server?

DECLARE @Name VarChar(100),@Type VarChar(20), @Schema VarChar(20)
SELECT @Name = 'usp_GetAllThis', @Type = 'PROCEDURE', @Schema = 'ss'

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(@Schema + '.' +  @Name))  EXECUTE('DROP ' + @Type + ' ' + @Schema + '.' + @Name)
GO
PRINT 'Creating ' + @Type + ' ' + @Schema + '.' + @Name

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

1. Create If Not Exists / Alter - this method is better for making non-permission destructive changes. What does that look like with SQL Server?

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

IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(@Schema + '.' +  @Name))
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