/*
*** Karl Mortimer, John Thorpe
*/
--  Tested on SQL SERVER 7.0 & 2000
--  Supports upto 16 column compound keys
--
--  procedure to create 'create index...' statements and 'drop index...' statements for a given table
--  option to include or exclude Primary keys and create 'alter table...' statements
--  option to include or exclude Foreign Keys and create 'alter table...' statements
--  excludes unique constraints (as these do not include indexing) and will not include
--  sorted_data and sorted_data_reorg parameters to the statement
--  as they must be specified manually.  This means that if data needs
--  to be sorted when the index is put on, a dbcc reindex must be
--  executed for that table afterwards with the sorted_data / sorted_data_reorg
--  parameters correctly set
--
if exists (select * from sysobjects where id = object_id('dbo.sp_IndexKeyText') and sysstat & 0xf = 4)
    drop procedure dbo.sp_IndexKeyText
GO

create procedure sp_IndexKeyText

@Options tinyint = 25,	-- bitmap of options. First bit determines whether indexes are included
			-- Second bit determines whether primary keys are included
			-- Third bit determines whether foreign keys are included 
			-- Fourth bit determines whether drop statements are scripted
			-- Fifth bit determines whether create statements are scripted
			-- Default bit 1 true for indexes only, bits 4 and 5 true for both to be scripted.
@Tablename VARCHAR(50) = NULL --optional tablename to drop indexes on a given table


as

set nocount on

if @Options & 8 <> 8 AND @Options & 16 <> 16 --Both Create and Drop script options turned OFF
begin
	RAISERROR('Both drop and create scripting switched off, no output would be produced',10,1)
	RETURN
end

if @options = 0
begin
	RAISERROR('All Index options turned off - no output will be produced',10,1)
	RETURN
end

if @Tablename is not null
and @Tablename not in (select name from sysobjects where type = 'U')
begin
	RAISERROR('Invalid value given for @Tablename - table does not exist in current database',10,1)
	RETURN
end

declare @objid int        /* the object id of the table */
declare @indid int        /* the index id of an index */
declare @keys varchar(200)    /* string to build up index key in */
declare @inddesc varchar(68)    /* string to build up index desc in */
declare @dbname varchar(30),
	@primary BIT,
        @unique BIT,
        @clustered BIT,
        @fillfactor TINYINT,
        @ignoredupkey BIT,
        @allowduprow BIT,
        @ignoreduprow BIT,
        @location sysname,
        @index_name VARCHAR(30),
        @with VARCHAR(100),
        @IndexString VARCHAR(255),
        @objname VARCHAR(50),
	@Title varchar(30),
	@empty varchar(1),
	@groupid smallint,
	@ExecString varchar(200)

select @empty = '' -- Empty string for concatenation

create table #sysfilegroups(
		groupid smallint not null,
		allocpolicy smallint not null,
		status int not null,
		groupname sysname not null)

select @ExecString = 'select * from '+DB_NAME(0)+'..sysfilegroups'

insert into #sysfilegroups
exec (@ExecString)

IF @Tablename is NULL
    BEGIN

        DECLARE CurTables CURSOR FOR
        SELECT name
        FROM sysobjects
        WHERE type = 'U'
        ORDER BY name

    END
ELSE
    BEGIN

        DECLARE CurTables CURSOR FOR
        SELECT name
        FROM sysobjects
        WHERE type = 'U'
        AND name = @Tablename
        ORDER BY name

    END

OPEN CurTables
FETCH NEXT FROM CurTables INTO @objname

CREATE TABLE #CREATE (description VARCHAR(255))

CREATE TABLE #DROP (description VARCHAR(255))

WHILE @@FETCH_STATUS = 0
BEGIN
    /*
    **  Check to see the the table exists and initialize @objid.
    */
    select @objid = object_id(@objname)


    /*
    **  Table doesn't exist so return.
    */
    if @objid is NULL
        begin
            GOTO NEXT_TABLE
        end
    

    /*
    **  See if the object has any indexes.
    **  Since there may be more than one entry in sysindexes for the object,
    **  this select will set @indid to the index id of the first index.
    */
    select @indid = min(indid) from sysindexes
        where id = @objid
            and indid > 0
            and indid < 255

    /*
    **  If no indexes, return.
    */
    if @indid is NULL
        begin
            GOTO NEXT_TABLE
        end
    

    set nocount on

    /*
    **  Now check out each index and figure out its type and keys 
    */

    while @indid IS NOT NULL
    begin
	select @primary = 0

        /*
        ** See if the index is primary key constraint (0x800).
        */

        if exists (select *
            from master.dbo.spt_values v, sysindexes i
            where i.status & v.number = v.number
                and v.type = 'I'
                and v.number = 2048
                 and i.id = @objid
                 and i.indid = @indid)
            BEGIN
		if @Options & 2 = 2 -- Primary Key Flag Set   
                	-- if primary key, set flag
                	select @primary = 1
		else GOTO Ignore_index
            END

	if @primary = 0 and @Options & 1 <> 1 GOTO Ignore_index

        /*
        **  See if the index is unique key constraint (0x1000).
         */
        if exists (select * from master.dbo.spt_values v, sysindexes i
                where i.status & v.number = v.number
                and v.type = 'I'
                and v.number = 4096
                and i.id = @objid
                 and i.indid = @indid)
            BEGIN
                -- if Unique key, ignore index
                GOTO Ignore_index
            END


        /*
        **  First we'll figure out what the keys are.
        */
        declare @k int
        declare @thiskey varchar(30)
        declare @lastindid int

        select @k = 1

        while @k <= 16
        begin
            select @thiskey = index_col(@objname, @indid, @k)

            if @thiskey is null
                goto keysdone

            if @k = 1
                select @keys = '['+index_col(@objname, @indid, @k)+']'
            else
                select @keys = @keys + ', ['+index_col(@objname, @indid, @k)+']'

            /*
            **  Increment @k so it will check for the next key.
            */
            select @k = @k + 1

        end


        /*
        **  When we get here we now have all the keys.
        */
        keysdone:

        /*
        **  Initialize the index description by figuring out if it's a
        **  clustered or nonclustered index.
        */
        if @indid = 1
            select @clustered = 1 -- clustered

        if @indid > 1
            select @clustered = 0 -- non-clustered

        /*
        **  Now we'll check out the status bits for this index and
        **  build an english description from them.
        */

        /*
        **  See if the index is ignore_dupkey (0x01).
        */
        if exists (select *
            from master.dbo.spt_values v, sysindexes i
            where i.status & v.number = v.number
                and v.type = 'I'
                and v.number = 1
                and i.id = @objid
                and i.indid = @indid)
            BEGIN
                SELECT @ignoredupkey = 1
            END
        else
            BEGIN
                SELECT @ignoredupkey = 0
            END

        /*
        **  See if the index is unique (0x02).
        */
        if exists (select *
            from master.dbo.spt_values v, sysindexes i
            where i.status & v.number = v.number
                and v.type = 'I'
                and v.number = 2
                and i.id = @objid
                and i.indid = @indid)
            BEGIN
                SELECT @unique  = 1
            END
        else
            BEGIN
                SELECT @unique = 0
            END

        /*
        **  Add the location of the data.
	*/
    
	select @location = fg.groupname from #sysfilegroups fg, sysindexes i  
					where fg.groupid = i.groupid 
					and i.id = @objid
                			and i.indid = @indid

        -- get the index_name and fillfactor

            select @index_name = name,
                   @fillfactor = origfillfactor
                from sysindexes
                where id = @objid
                    and indid = @indid

        -- set with clause
        SELECT @with = NULLIF('FILLFACTOR = '+CONVERT(VARCHAR,@fillfactor)+',', 'FILLFACTOR = 0,')

	select @with = case when @with is null then @empty else @with end
                       +CASE @ignoredupkey
                               WHEN 1 THEN 'IGNORE_DUP_KEY '
                               WHEN 0 THEN @empty
                               END

        -- if @with clause contains anything add keyword 'WITH' to beginning of string

        IF @with <> @empty
            BEGIN
                SELECT @with = ' WITH '+@with
            END

	if @primary = 0
	begin
		select @IndexString = NULLIF(' ON '+@Location, ' ON PRIMARY')



		select @IndexString = case when @IndexString is null then @empty else @IndexString end
	
        	-- print create index statement out
        	SELECT @IndexString =
                	'CREATE '+CASE @unique
                         	WHEN 1 THEN 'UNIQUE '
                         	WHEN 0 THEN @empty
                         	END
                        	+CASE @clustered
                         	WHEN 1 THEN ' CLUSTERED '
                         	WHEN 0 THEN ' NONCLUSTERED '
                         	END
                	+' INDEX ['+@index_name +'] '
                	+'ON ['+@objname+'] ('+@keys+')'
                	+case when @with is null then @empty else @with end
                	+@Indexstring
	end
	else
	begin
		select @IndexString = NULLIF('ON '+@Location, 'ON PRIMARY')

		select @IndexString = case when @IndexString is null then @empty else @IndexString end

		-- print alter table statement out
		select @IndexString  =
			'ALTER TABLE ['+@objname+'] ADD CONSTRAINT ['+@index_name+'] PRIMARY KEY '
				+CASE @clustered
                         	WHEN 1 THEN 'CLUSTERED '
                         	WHEN 0 THEN 'NONCLUSTERED '
                         	END
				+'('+@keys+')'
                		+case when @with is null then @empty else @with end
                		+@IndexString
	end

        IF @Options & 16 = 16 --create flag ON
        BEGIN
            SELECT @IndexString = @IndexString + CHAR(13)+'GO'+CHAR(13)+CHAR(13)

            INSERT INTO #CREATE(description) VALUES (@IndexString)
        END

        IF @Options & 8 = 8 --drop flag ON
        BEGIN
		if @primary = 0
            		SELECT @IndexString = 'DROP index ['+@objname+'].['+@index_name+']'
		else
			SELECT @IndexString = 'ALTER TABLE ['+@objname+'] DROP CONSTRAINT ['+@index_name+']'

            SELECT @IndexString = @IndexString + CHAR(13)+'GO'+CHAR(13)+CHAR(13)

            INSERT INTO #DROP(description) VALUES (@IndexString)
        END

        ignore_index:


        /*
        **  Now move @indid to the next index.
        */
        select @lastindid = @indid
        select @indid = NULL
        select @indid = min(indid)
            from sysindexes
            where id = @objid
                and indid > @lastindid
                and indid < 255
    end

    
    NEXT_TABLE:

    FETCH NEXT FROM CurTables INTO @objname

END -- WHILE loop

/*
 **  Now print out the contents of the temporary index table.
 */
SET nocount on
IF @Options & 8 = 8 --drop flag ON
begin
	if @Options & 2 = 2 AND @Options & 1 = 1 SELECT description AS 'Index / Primary Key Drops' FROM #drop
	if @Options & 2 <> 2 AND @Options & 1 = 1 SELECT description AS 'Index Drops' FROM #drop
	if @Options & 2 = 2 AND @Options & 1 <> 1 SELECT description AS 'Primary Key Drops' FROM #drop
end

IF @Options & 16 = 16 --create flag ON
begin
	if @Options & 2 = 2 AND @Options & 1 = 1 SELECT description AS 'Index / Primary Key Creates' FROM #create
	if @Options & 2 <> 2 AND @Options & 1 = 1 SELECT description AS 'Index Creates' FROM #create
	if @Options & 2 = 2 AND @Options & 1 <> 1 SELECT description AS 'Primary Key Creates' FROM #create
end

CLOSE CurTables
DEALLOCATE CurTables

drop table #create
drop table #drop

set nocount off

/*
 ***	Foreign Key code generation 
 */

IF @Options & 4 = 4 -- Foreign Key flag ON
begin

	SET nocount on
	IF @Options & 8 = 8 --drop flag ON
	begin
		select 'Foreign Key drops' = 'alter table ['+object_name(fkeyid)+']'+char(13)
		+'drop constraint ['+object_name(constid)+']'+char(13)+char(13)+'GO'+char(13)+char(13)
		from sysreferences
		where OBJECT_NAME(rkeyid) like isnull(@Tablename,'%')
		order by object_name(fkeyid)
	end

	IF @Options & 16 = 16 --create flag ON
	begin
		select 'Foreign Key creates' = 'alter table ['+object_name(fkeyid)+'] '+char(13)
		+'add constraint ['+object_name(constid)+'] '
		+'Foreign key(['+f1.name+']'
		+case when f2.name is null then @empty else '['+f2.name+']' end
		+case when f3.name is null then @empty else '['+f3.name+']' end
		+case when f4.name is null then @empty else '['+f4.name+']' end
		+case when f5.name is null then @empty else '['+f5.name+']' end
		+case when f6.name is null then @empty else '['+f6.name+']' end
		+case when f7.name is null then @empty else '['+f7.name+']' end
		+case when f8.name is null then @empty else '['+f8.name+']' end
		+case when f9.name is null then @empty else '['+f9.name+']' end
		+case when f10.name is null then @empty else '['+f10.name+']' end
		+case when f11.name is null then @empty else '['+f11.name+']' end
		+case when f12.name is null then @empty else '['+f12.name+']' end
		+case when f13.name is null then @empty else '['+f13.name+']' end
		+case when f14.name is null then @empty else '['+f14.name+']' end
		+case when f15.name is null then @empty else '['+f15.name+']' end
		+case when f16.name is null then @empty else '['+f16.name+']' end
		+')'+char(13)
		+'references ['+object_name(rkeyid)+'] (['+c1.name+']'
		+case when c2.name is null then @empty else '['+c2.name+']' end
		+case when c3.name is null then @empty else '['+c3.name+']' end
		+case when c4.name is null then @empty else '['+c4.name+']' end
		+case when c5.name is null then @empty else '['+c5.name+']' end
		+case when c6.name is null then @empty else '['+c6.name+']' end
		+case when c7.name is null then @empty else '['+c7.name+']' end
		+case when c8.name is null then @empty else '['+c8.name+']' end
		+case when c9.name is null then @empty else '['+c9.name+']' end
		+case when c10.name is null then @empty else '['+c10.name+']' end
		+case when c11.name is null then @empty else '['+c11.name+']' end
		+case when c12.name is null then @empty else '['+c12.name+']' end
		+case when c13.name is null then @empty else '['+c13.name+']' end
		+case when c14.name is null then @empty else '['+c14.name+']' end
		+case when c15.name is null then @empty else '['+c15.name+']' end
		+case when c16.name is null then @empty else '['+c16.name+']' end
		+')'+char(13)
		+char(13)
		from sysreferences,
		syscolumns c1,
		syscolumns c2,
		syscolumns c3,
		syscolumns c4,
		syscolumns c5,
		syscolumns c6,
		syscolumns c7,
		syscolumns c8,
		syscolumns c9,
		syscolumns c10,
		syscolumns c11,
		syscolumns c12,
		syscolumns c13,
		syscolumns c14,
		syscolumns c15,
		syscolumns c16,
		syscolumns f1,
		syscolumns f2,
		syscolumns f3,
		syscolumns f4,
		syscolumns f5,
		syscolumns f6,
		syscolumns f7,
		syscolumns f8,
		syscolumns f9,
		syscolumns f10,
		syscolumns f11,
		syscolumns f12,
		syscolumns f13,
		syscolumns f14,
		syscolumns f15,
		syscolumns f16
		WHERE c1.colid =* rkey1
		AND c1.id =* rkeyid
		AND c2.colid =* rkey2
		AND c2.id =* rkeyid
		AND c3.colid =* rkey3
		AND c3.id =* rkeyid
		AND c4.colid =* rkey4
		AND c4.id =* rkeyid
		AND c5.colid =* rkey5
		AND c5.id =* rkeyid
		AND c6.colid =* rkey6
		AND c6.id =* rkeyid
		AND c7.colid =* rkey7
		AND c7.id =* rkeyid
		AND c8.colid =* rkey8
		AND c8.id =* rkeyid
		AND c9.colid =* rkey9
		AND c9.id =* rkeyid
		AND c10.colid =* rkey10
		AND c10.id =* rkeyid
		AND c11.colid =* rkey11
		AND c11.id =* rkeyid
		AND c12.colid =* rkey12
		AND c12.id =* rkeyid
		AND c13.colid =* rkey13
		AND c13.id =* rkeyid
		AND c14.colid =* rkey14
		AND c14.id =* rkeyid
		AND c15.colid =* rkey15
		AND c15.id =* rkeyid
		AND c16.colid =* rkey16
		AND c16.id =* rkeyid
		AND f1.colid =* fkey1
		AND f1.id =* fkeyid
		AND f2.colid =* fkey2
		AND f2.id =* fkeyid
		AND f3.colid =* fkey3
		AND f3.id =* fkeyid
		AND f4.colid =* fkey4
		AND f4.id =* fkeyid
		AND f5.colid =* fkey5
		AND f5.id =* fkeyid
		AND f6.colid =* fkey6
		AND f6.id =* fkeyid
		AND f7.colid =* fkey7
		AND f7.id =* fkeyid
		AND f8.colid =* fkey8
		AND f8.id =* fkeyid
		AND f9.colid =* fkey9
		AND f9.id =* fkeyid
		AND f10.colid =* fkey10
		AND f10.id =* fkeyid
		AND f11.colid =* fkey11
		AND f11.id =* fkeyid
		AND f12.colid =* fkey12
		AND f12.id =* fkeyid
		AND f13.colid =* fkey13
		AND f13.id =* fkeyid
		AND f14.colid =* fkey14
		AND f14.id =* fkeyid
		AND f15.colid =* fkey15
		AND f15.id =* fkeyid
		AND f16.colid =* fkey16
		AND f16.id =* fkeyid
		AND OBJECT_NAME(rkeyid) like isnull(@Tablename,'%')
	end

	SET nocount off
end

drop table #sysfilegroups

set nocount off

return

go