SQL SERVER OBJECT TEMPLATES -- INDEX CREATION

Template for creating index script in sql server:
--------------------------------------------------------


/******************************************************************
======== BEGIN MAINTENANCE WRAPPER ==========
******************************************************************/
-- Check if the index already exists

IF EXISTS ( SELECT 1
FROM sysindexes sysind
,sysobjects sysobj
WHERE sysind.name = 'IX_INDEX_NAME'
AND sysind.id = sysobj.id
AND sysobj.name = 'TBL_TABLE_NAME'
AND sysobj.uid = user_id('dbo')
AND sysobj.type = 'U')
-- P = Proc; V = View; U = Table; TR = Trigger

BEGIN
-- If it exists, drop to recreate the index on table TBL_TABLE_NAME
DROP INDEX dbo.TBL_TABLE_NAME.IX_INDEX_NAME
PRINT 'INDEX IX_INDEX_NAME OF TABLE TBL_TABLE_NAME DROPPED SUCCESSFULLY'
END
ELSE
BEGIN
PRINT 'INDEX IX_INDEX_NAME OF TABLE TBL_TABLE_NAME DROPPED FAILED'
END
GO
/******************************************************************
======== END MAINTENANCE WRAPPER ==========
******************************************************************/
/********************************************************
** REVISION HISTORY : *
** ====================== *
** DATE                     VERSION           NAME                    COMMENTS *
** ---------- ------- ----------------- -----------------*
** MM/DD/CCYY          1.0               Developer Name           Initial Draft *
** *
*****************************************************************/

CREATE UNIQUE CLUSTERED INDEX IX_INDEX_NAME
ON TBL_TABLE_NAME
( COLUMN_NAME1

,COLUMN_NAME2
)

GO

/******************************************************************
======== BEGIN MAINTENANCE WRAPPER ==========
******************************************************************/
-- Check for errors in creating the index

IF EXISTS (SELECT 1
FROM sysindexes sysind
,sysobjects sysobj
WHERE sysind.name = 'IX_INDEX_NAME'
AND sysind.id = sysobj.id
AND sysobj.name = 'TBL_TABLE_NAME'
AND sysobj.uid = user_id('dbo')
AND sysobj.type = 'U')
-- P = Proc; V = View; U = Table; TR = Trigger
BEGIN
PRINT 'INDEX IX_INDEX_NAME ON TABLE TBL_TABLE_NAME CREATED SUCCESSFULLY'
END
ELSE
BEGIN
PRINT 'INDEX IX_INDEX_NAME ON TABLE TBL_TABLE_NAME CREATION FAILED'
END
GO
/******************************************************************
======== END MAINTENANCE WRAPPER ==========
******************************************************************/

This entry was posted in . Bookmark the permalink.

Leave a reply