Sep 1

Written by: Steve Gray
9/1/2010 10:30 AM 

I often create long lists of ‘create index’ statements that go along with my production stored procedures, the databases that I work on are usually not optimized for what I’m doing. There might be 50 or 100 indexes.

I’ve developed this script that creates indexes. The deal is that you submit the table name, index name, a list of fields in the index, a CLUSTERED flag, a UNIQUE flag, and a field list of INCLUDEs.

The script tests to see if the index exists and if it does, it reports that the ‘script already exists’, although you could easily modify that to drop the script for recreation.

If the index doesn’t exist, the index is created, and the script echos back the creation syntax.

The end result is that I can add on index to my list and then run the entire script. Needed indexes are created, and existing ones are ignored.

 

 

IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'dbo.tb_CreateIndex') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
 DROP PROCEDURE dbo.tb_CreateIndex
GO
--================================================================================
--creates an index, if the specified index does not exist
--does not return errors
--================================================================================
CREATE PROCEDURE dbo.tb_CreateIndex
/*
tb_CreateIndex 'actindx','GL00105','actindx,ACTNUMBR_3', '','','actnumbr_1, actnumbr_2'
exec tb_CreateIndex 'tbACTINDX' , 'GL00100' , 'ACTINDX ASC' ,'clustered', 'UNIQUE' ,''
*/
@indexName varchar(256),
@tableName varchar(256),
@fieldList varchar(256),
@clustered varchar(15),   --set to 'CLUSTERED' for a clustered index, otherwise set to ''
@unique varchar(15),      --set to 'UNIQUE' for a unique index, otherwise set to ''
@includeList varchar(255) --if the index specifies an 'include list', set to comma-separated fields, otherwise ''
                          --i.e. 'field1,field2'
AS

declare @sql varchar(256)
if not exists(
    select 1
        from sys.indexes i
            join sys.objects o on i.object_id = o.object_id
        where o.name = @tablename
             and i.name = @indexname
    ) begin
    
    set @sql = 'create ' 
    if @unique > '' BEGIN
        set @sql = @sql + 'UNIQUE '
    end
    if @clustered > '' BEGIN
        set @sql = @sql + 'CLUSTERED '
    end
    set @sql = @SQL + 'index ' + @indexName + ' on ' + @tablename + '(' + @fieldList + ') '
    if @includeList > '' BEGIN
        set @sql = @sql + 'INCLUDE (' + @includeList + ')'
    end
    print @sql
    exec (@sql)
end else begin
    print 'index ' + @tablename + '.' + @indexname + ' already exists'
end