CODEWITHSUNDEEP
t-sql
Levin
Levin

Reputation: 2005

tsql to generate index creation text, then evaluate it

I have a script that creates and populates a bunch of tables from data we receive as text files. After loading the data into sql server 2008, I want to add indexes. The cut-and-paste-created script I'm using is shown below.

Is there a way in TSQL to create a function that will generate and then evaluate the DDL commands? The function would take parameters of table name, column name(s) and clustered/not. If there would have to be a separate function for 1 column/2 column indexes, I'd still be interested.

I'm envisioning something like:

hypotheticalFunction('clustered', 'precCdaEarn', 'account', 'seq')
hypothericalFunction('nonClustered', 'flats', 'vendorAcct')

and that would check if an acct called "idx_acct_seq" exists on table precCdaEarn, and drop it if so, then create it as clustered index. (dropping it just in case i change the definition). And create/recreate a nonclustered index on dbo.flats named "idx_vendorAcct"

========= this is what have now using cut & paste, apologies for the erratic spacing ==============

IF EXISTS (Select 'X' 
  FROM sysindexes
    WHERE id = (SELECT OBJECT_ID('precCdaEarn'))
      and name = 'idx_account_seq')
     DROP index precCdaEarn.idx_account_seq 
   Create nonclustered index idx_account_seq
      ON precCdaEarn(account, seq)

;

IF EXISTS (Select 'X' 
FROM sysindexes
      WHERE id = (SELECT OBJECT_ID('acct'))
             and name = 'idx_c_precAcct_precMod')
 DROP index acct.idx_c_precAcct_precMod
CREATE CLUSTERED INDEX index idx_c_precAcct_precMod
    ON acct(precAcct, precMod)

Upvotes: 0

Views: 564

Answers (1)

Brimstedt
Brimstedt

Reputation: 3140

AFAIK you cannot create a function for this, but with a stored procedure it's no problems.

Something like:

IF(OBJECT_ID('PRC_CREATE_INDEX', N'P') IS NOT NULL)
    DROP PROCEDURE PRC_CREATE_INDEX
GO

CREATE PROCEDURE dbo.PRC_CREATE_INDEX 
(
    -- Table name to add index to
    @pTablename AS VARCHAR(120)

    -- Index name to add/change
,   @pIndexname AS VARCHAR(120)         

    -- UNIQUE, CLUSTERED or other definition
,   @pNewIndexDef AS VARCHAR(255) = ''

    -- Columns in index     
,   @pNewIndexCols AS VARCHAR(255)

    -- Recreate even if same 
,   @pForceRecreate AS SMALLINT = 0

    -- Additional columns for INCLUDE
,   @pColumnsInclude AS VARCHAR(255) = ''
)
AS

------------------------------------
--  Variabels
------------------------------------
    DECLARE @SQL_DROP AS NVARCHAR(1000)
    ,   @SQL_CREATE AS NVARCHAR(1000)
    ,   @retval AS INTEGER
    ,   @rowcount AS INTEGER

    DECLARE @oldDescription AS VARCHAR(255)
    ,   @newUnique AS SMALLINT
    ,   @newClustered AS SMALLINT
    ,   @newIndexDef AS VARCHAR(255)
    ,   @newDescription AS VARCHAR(255)
    ,   @drop_index AS BIT
    ,   @temp AS VARCHAR(MAX)


------------------------------------
--  Initiate
------------------------------------
    SET XACT_ABORT ON
    SET NOCOUNT ON


-- Table exists?

    IF(OBJECT_ID(@pTablename) IS NULL)
    BEGIN
        PRINT   'Error: Table does not exist: ' + @pTablename + 
            ' ('  + 'creation of index ''' + @pTablename +  '.' + @pIndexname + ''')'
        RETURN (1)
    END


-----------------------------------------------------------------------------------------------------
-- New index
-----------------------------------------------------------------------------------------------------


-- Initiate (Remove blanks, get unique/clustered info)

    SELECT  @newIndexDef = @pNewIndexDef
    -- Remove NONCLUSTERED
    ,   @newIndexDef = REPLACE(@newIndexDef, 'nonclustered', '')
    -- CLUSTERED?
    ,   @newClustered = CASE WHEN PATINDEX('%clustered%', LOWER(@newIndexDef)) > 0 THEN 1 ELSE 0 END
    ,   @newIndexDef = REPLACE(@newIndexDef, 'clustered', '')
    -- UNIQUE?
    ,   @newUnique = CASE WHEN PATINDEX('%unique%', LOWER(@pNewIndexDef)) > 0 THEN 1 ELSE 0 END
    ,   @newIndexDef = REPLACE(@newIndexDef, 'unique', '')
    -- Remove blanks etc.
    ,   @pNewIndexCols = REPLACE(@pNewIndexCols, ' DESC', '#DESC')
    ,   @newIndexDef = REPLACE(@newIndexDef, ' ', '')
    ,   @newIndexDef = REPLACE(@newIndexDef, ',', '')
    ,   @pNewIndexCols = REPLACE(@pNewIndexCols, ' ', '')
    ,   @pColumnsInclude = REPLACE(@pColumnsInclude, ' ', '')


-- Correct defintion?

    IF LEN(@newIndexDef) > 0
    BEGIN
        RAISERROR ('Index defintion ''%s'' is incorrect for index ''%s.%s''!', 11, 11, @pNewIndexDef, @pTablename, @pIndexname)
        RETURN (1)
    END     

    IF @newClustered = 1 AND LEN(@pColumnsInclude) > 0
    BEGIN
        RAISERROR ('Cannot specify included columns for a clustered index (''%s.%s'')!', 11, 11, @pIndexname, @pTablename)
        RETURN (1)
    END

    IF @pNewIndexCols LIKE '%#DESC%'
    BEGIN
        RAISERROR ('Descending sort direction not supported (''%s.%s'')!', 11, 11, @pTablename, @pIndexname)
        RETURN (1)
    END

    IF  @newClustered = 1 AND EXISTS
    ( 
        SELECT  1
        FROM    sys.indexes (NOLOCK)
        WHERE   object_id = OBJECT_ID(@pTablename, N'U')
        AND name <> @pIndexname
        AND type = 1            -- CLUSTERED
        AND is_hypothetical = 0
    )   
    BEGIN
        SELECT  @temp = name
        FROM    sys.indexes (NOLOCK)
        WHERE   object_id = OBJECT_ID(@pTablename, N'U')
        AND name <> @pIndexname
        AND type = 1            -- CLUSTERED
        AND is_hypothetical = 0

        RAISERROR ('Cannot add CLUSTERED index (''%s.%s'') - table already has a clustered index (''%s'')!', 11, 11, @pTablename, @pIndexname, @temp)
        RETURN (1)
    END


-- Prepare SQL

    -- CREATE
    SELECT  @SQL_CREATE = ''
    +   'CREATE '
    +   CASE WHEN @newUnique = 1 THEN 'UNIQUE ' ELSE '' END
    +   CASE WHEN @newClustered = 1 THEN 'CLUSTERED ' ELSE 'NONCLUSTERED ' END
    +   'INDEX ' + @pIndexname + ' '
    +   'ON ' + @pTablename + ' ' 
    +   '(' + @pNewIndexCols + ')'
    +   CASE WHEN LEN(@pColumnsInclude) > 0 THEN ' INCLUDE (' + @pColumnsInclude + ')' ELSE '' END

    -- Description
    SELECT  @newDescription = ''
    +   CASE WHEN @newUnique = 1 THEN 'UNIQUE ' ELSE '' END
    +   CASE WHEN @newClustered = 1 THEN 'CLUSTERED ' ELSE 'NONCLUSTERED ' END
    +   '(' + @pNewIndexCols + ')'
    +   CASE WHEN LEN(@pColumnsInclude) > 0 THEN ' INCLUDE (' + @pColumnsInclude + ')' ELSE '' END

    -- DROP
    SET @SQL_DROP = ''
    +   'DROP INDEX ' + @pTablename
    +   '.' + @pIndexname



-----------------------------------------------------------------------------------------------------
-- Current index
-----------------------------------------------------------------------------------------------------

-- Initiate 

    SELECT  @drop_index = 0

-- Get definition/description and check if recreation is needed

    IF EXISTS
    (   
        SELECT  1
        FROM    sys.indexes (NOLOCK)
        WHERE   object_id = OBJECT_ID(@pTablename, N'U')
        AND name = @pIndexname
    )
    BEGIN   

    -- Description

        SELECT  @oldDescription = ''
        +   CASE WHEN i.is_unique = 1 THEN 'UNIQUE ' ELSE '' END
        +   CASE 
                WHEN    i.type = 1 THEN 'CLUSTERED ' 
                WHEN    i.type = 2 THEN 'NONCLUSTERED ' 
                ELSE    '? '
            END
        +   '(' + STUFF(
                (
                    SELECT  ',' + COL_NAME(ic.object_id, ic.column_id)
                    FROM    sys.index_columns ic (NOLOCK)
                    WHERE   ic.object_id = i.object_id
                    AND ic.index_id = i.index_id
                    AND ic.is_included_column = 0
                    ORDER   BY ic.key_ordinal
                    FOR XML PATH('')
                )
            , 1, 1, '') + ')'
        +   ISNULL(' INCLUDE (' + STUFF(
                (
                    SELECT  ',' + COL_NAME(ic.object_id, ic.column_id)
                    FROM    sys.index_columns ic (NOLOCK)
                    WHERE   ic.object_id = i.object_id
                    AND ic.index_id = i.index_id
                    AND ic.is_included_column = 1
                    ORDER   BY ic.index_column_id    -- Or column_id???
                    FOR XML PATH('')
                )
            , 1, 1, '') + ')', '')
        FROM    sys.indexes i (NOLOCK)
        WHERE   i.object_id = OBJECT_ID(@pTablename, N'U')
        AND i.name = @pIndexname

    -- Exit?

        -- If not changed and no force of recreation
        IF  @oldDescription = @newDescription
            AND
            @pForceRecreate = 0
        BEGIN       
            RETURN 0
        END


    -- We should drop current index..

        SET @drop_index = 1
    END



-----------------------------------------------------------------------------------------------------
-- Execute SQL
-----------------------------------------------------------------------------------------------------

-- Exec

    IF @drop_index = 1
        EXEC    sp_executesql @SQL_DROP

    IF LEN(@pNewIndexCols) > 0
        EXEC    sp_executesql @SQL_CREATE


-- Message

    IF LEN(@pNewIndexCols) > 0 AND @drop_index = 0
        PRINT   'Created index ' + @pTablename + '.' + @pIndexname + ' (' + @newDescription + ')'
    ELSE IF LEN(@pNewIndexCols) > 0 AND @drop_index = 1
        PRINT   'Recreated index ' + @pTablename + '.' + @pIndexname + CHAR(10) +
            '   From: ' + @oldDescription + CHAR(10) +
            '   To:   ' + @newDescription
    -- Well, this will perhaps occur when and if this proc is changed...
    ELSE IF @drop_index = 1
        PRINT 'Removed index ' + @pTablename + '.' + @pIndexname + ' (' + @oldDescription + ')'


-----------------------------------------------------------------------------------------------------
-- Exit
-----------------------------------------------------------------------------------------------------
    RETURN (0)
GO

Use it like:

EXEC    dbo.PRC_CREATE_INDEX
    @pTablename = 'someTable'
,   @pIndexname = 'idx_someTable'
,   @pNewIndexDef = 'CLUSTERED' 
,   @pNewIndexCols = 'some, columns, listed'

Upvotes: 1

Related Questions