SQL Server: create table indexes from meta data

How to create a meta data driven table index routine using T-SQL.

1)  Metadata container


CREATE TABLE [dbo].[ETL_INDEXES](
[TABLE_NAME] [varchar](100) NOT NULL,
[INDEX_TYPE] [varchar](2) NOT NULL,
[INDEX_NAME] [varchar](100) NOT NULL,
[DROP_FL] [bit] NOT NULL,
[CREATE_FL] [bit] NOT NULL,
[REBUILD_FL] [bit] NOT NULL,
[COLUMNS] [varchar](max) NOT NULL
) ON [PRIMARY]

2)  Create T SQL routine to loop through meta data table based on passed table variable


CREATE PROCEDURE [dbo].[sp_loop_create_table_indexes]
@table_name varchar(100)
AS

/*
@table_name: target table

*/
-->This stored procedure will read meta data stored in etl_indexes
--and perform creates where flagged

--*declare working variables
DECLARE @cursor CURSOR
DECLARE @vTABLE_NAME varchar(100)
DECLARE @vINDEX_TYPE varchar(2)
DECLARE @vINDEX_NAME varchar(100)
DECLARE @vDROP_FL bit
DECLARE @vCREATE_FL bit
DECLARE @vREBUILD_FL bit
DECLARE @vCOLUMNS varchar(max)

SET @cursor  = CURSOR FOR
Select distinct TABLE_NAME
, INDEX_TYPE
, INDEX_NAME
, DROP_FL
, CREATE_FL
, REBUILD_FL
, COLUMNS
from operations.dbo.ETL_INDEXES where TABLE_NAME=@table_name
OPEN @cursor

FETCH NEXT FROM @cursor INTO  @vTABLE_NAME ,@vINDEX_TYPE ,@vINDEX_NAME
 ,@vDROP_FL ,@vCREATE_FL ,@vREBUILD_FL  ,@vCOLUMNS

WHILE @@FETCH_STATUS <> -1
BEGIN

exec dbo.sp_create_table_indexes @vTABLE_NAME ,@vINDEX_TYPE ,@vINDEX_NAME
 ,@vDROP_FL ,@vCREATE_FL ,@vREBUILD_FL  ,@vCOLUMNS

FETCH NEXT FROM @cursor INTO @vTABLE_NAME ,@vINDEX_TYPE ,@vINDEX_NAME
 ,@vDROP_FL ,@vCREATE_FL ,@vREBUILD_FL  ,@vCOLUMNS
END
CLOSE @cursor  
DEALLOCATE @cursor
GO

3) Create T SQL routine to create indexes (sub routine to previous step)


CREATE PROCEDURE [dbo].[sp_create_table_indexes]
@table_name varchar(100)
,@index_type varchar(2)
,@index_name varchar(100)
,@drop_fl bit
,@create_fl bit
,@rebuild_fl bit
,@columns varchar(max)

AS

/*
@table_name: target table name
@index_type: IX (nonclustered), CX (clustered), UX (unique nonclustered),
PK (primary key constraint, unique clustered)
**NOTE: PK's can only be applied to non-null columns, there can be only
one clustered index, by default PK's are clustered with this script
@index_name: Name of table index
@drop_fl: triggers index drop
@create_fl: triggers index creation
@rebuild_fl: triggers index rebuild
@columns: list of columns to index
*/

-->This stored procedure executes a create index based on passed variables

--*declare working variables
declare @PK varchar(4000)
declare @IX varchar(4000)
declare @CX varchar(4000)
declare @UX varchar(4000)

IF @index_type='PK' BEGIN
set @PK='
ALTER TABLE [dbo].['+@table_name+'] ADD  CONSTRAINT ['+@index_name+'] PRIMARY KEY CLUSTERED
('+@columns+') WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
'
exec (@PK)
END

IF @index_type ='IX' BEGIN
SET @IX='
CREATE NONCLUSTERED INDEX ['+@index_name+'] ON [dbo].['+@table_name+']
('+@columns+')WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
'
exec (@IX)
END

IF @index_type ='UX' BEGIN
SET @UX='
CREATE UNIQUE NONCLUSTERED INDEX ['+@index_name+'] ON [dbo].['+@table_name+']
('+@columns+')WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
'
exec (@UX)
END

IF @index_type ='CX' BEGIN
SET @CX='
CREATE CLUSTERED INDEX ['+@index_name+'] ON [dbo].['+@table_name+']
('+@columns+')WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
'
exec (@CX)
END

4)  Create T SQL drop index procedure (this is sub routine to steps above)


CREATE PROCEDURE [dbo].[sp_drop_table_indexes]
@table_name varchar(100)
,@index_type varchar(2)
,@index_name varchar(100)
,@drop_fl bit
,@create_fl bit
,@rebuild_fl bit
,@columns varchar(max)

AS

/*
@table_name: target table name
@index_type: IX (nonclustered), CX (clustered), UX (unique nonclustered),
PK (primary key constraint, unique clustered)
@index_name: Name of table index
@drop_fl: triggers index drop
@create_fl: triggers index creation
@rebuild_fl: triggers index rebuild
@columns: list of columns to index
*/

-->This stored procedure executes a drop index based on passed variables

--*declare working variables
declare @PK varchar(4000)
declare @I varchar(4000)

IF @index_type='PK' BEGIN
set @PK='
IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N''[dbo].['+@table_name+']'') AND name = N'''+@index_name+''')
ALTER TABLE [dbo].['+@table_name+'] DROP CONSTRAINT ['+@index_name+']
'
exec (@PK)
END

IF @index_type IN ('IX','UX','CX') BEGIN
SET @I='
IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N''[dbo].['+@table_name+']'') AND name = N'''+@index_name+''')
DROP INDEX ['+@index_name+'] ON [dbo].['+@table_name+'] WITH ( ONLINE = OFF )'
exec (@I)
END