Anytimescripts

Anytime Scripts

Folders

• AlterDatabase - New in v0.8.5!

• RunBeforeUp - New in v0.8.6!

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

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 + ']')

PRINT 'Creating ' + @Type + ' [' + @Schema + '].[' + @Name + ']'
GO

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

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 + ']'))
EXECUTE('CREATE ' + @Type + ' [' + @Schema + '].[' + @Name + '] AS SELECT * FROM sys.objects')

PRINT 'Creating/Updating ' + @Type + ' [' + @Schema + '].[' + @Name + ']'
GO

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

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

Indexes

DECLARE @Name VarChar(100),@TableName VarChar(100),@TableSchema VarChar(20)
SELECT @Name = 'IX_SomeTable_SomeColumns',@TableName='SomeTable', @TableSchema = 'dbo'

IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID('[' + @TableSchema + '].[' + @TableName + ']') AND UPPER(name) = UPPER(@Name))
EXECUTE('DROP INDEX [' + @Name + '] ON [' + @TableSchema + '].[' + @TableName + '] WITH ( ONLINE = OFF )')

PRINT 'Creating index [' + @Name + '] on table [' + @TableSchema + '].[' + @TableName + ']'
GO

CREATE INDEX [IX_SomeTable_SomeColumns] ON [shema].[tablename]
(
/* your index guts here */
)

/* optional where in SQL Server
WHERE [SomeColumn] IS NOT NULL
*/

Related

See OneTimeScripts
See EveryTimeScripts