Scripting indexes using an inline TVF

It is sometimes necessary for a person to script  indexes, so that they can synchronize indexes between databases, programmatically drop/create indexes in TSQL code, or create index scripts to the file system.  SSMS gives users multiple methods to script indexes to a file, the clip board, or to a new query window; however, SSMS does not really offer a solid method to script indexes programmatically.  I have chosen to use a TVF (Table Valued Function) because of its functionality.  TVFs allow code logic to be encapsulated in a single object like a view; however, unlike views, TVFs accept parameters. The use of parameters allow TVFs to behave like parameterized views; however, do not expect huge performance gains from query plan reuse and caching, for metadata queries. The main purpose for supplying parameters is to extend the functionality, such that it would allow me to script indexes to differing filegroups.  The results of this TVF will generate create/drop statements for all the indexes and some other data relative to the index, such as type and id. 

Now to the code:

I will be using the AdventureWorks database to demonstrate this code.  You can find a copy of the database here: http://www.codeplex.com/MSFTDBProdSamples/Release/ProjectReleases.aspx?ReleaseId=4004

The TVF has two input parameters.  The first parameter is @Dest_FG.  @Dest_FG accepts the filegroup that the indexes should be created on.  The second parameter is @Move_NCL_Idx_FG, which accepts the filegroup nonclustered indexes should be created on.  The nonclustered indexes can be created on a different filegroup than the clustered index or heap.

The code to create the TVF

IF  EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn_CreateIndexScripts]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
BEGIN
    DROP FUNCTION [dbo].[fn_CreateIndexScripts]
END
GO

/*
==============================================================================
Author: Adam Haines
------------------------------------------------------------------------------
Date: 5/1/2009
------------------------------------------------------------------------------
Inputs:
@Dest_FG - This is the filegroup we should create the index on.  The default
    value for this parameter is the Primary filegroup.
@Move_NCL_Idx_FG - Use this to move nonclustered indexes to a different
    filgroup than the clustered index/heap. If this value is null, the non
    clustered indexes are created in the same filegroup as the clustered
    index/heap.
-----------------------------------------------------------------------------
Description:
-----------------------------------------------------------------------------
This inline TVF select indexes that meet the parameters and creates drop and
and create scripts for each returned row.
------------------------------------------------------------------------------
Sample execution:
SELECT *
FROM [dbo].[usp_MoveTablesToNewFG]('PRIMARY','Test_FG',NULL)
==============================================================================
*/

CREATE FUNCTION dbo.fn_CreateIndexScripts
(
    @Dest_FG VARCHAR(50) = 'PRIMARY',
    @Move_NCL_Idx_FG VARCHAR(50) = NULL
)
RETURNS TABLE
RETURN
(
SELECT
    SCHEMA_NAME(t.schema_id) AS [SchemaName],
    t.[name] AS [TableName],
    FILEGROUP_NAME(i.[data_space_id]) AS [FilegroupName],
    i.[index_id] AS [IdxId],
    i.[type] AS [IndexType],
    --************* CREATE "CREATE" INDEX SCRIPT ******************
    CASE WHEN i.[type] = 3 THEN --0=heap,1=clustered,2=nonclustered,3=xml,4=spatial
        'CREATE ' +
            CASE WHEN xi.secondary_type IS NULL --when no secondary type is present we know it is a primary index
            THEN 'PRIMARY '
            ELSE ''
            END    + 'XML INDEX ' +
            QUOTENAME(xi.[name]) + ' ON ' +
            QUOTENAME(SCHEMA_NAME(t.schema_id)) + '.' +
            QUOTENAME(t.[name]) +
            '(' + QUOTENAME(COL_NAME(t.[object_id],ic.column_id)) + ')' +
            CASE WHEN xi.secondary_type IS NULL 
            THEN '' --when no secondary type is present we know it is a primary index and do need a using clause
            ELSE ' USING XML INDEX ' + QUOTENAME(PRI.name) +
                 ' FOR '+ 
                CASE xi.secondary_type
                    WHEN 'V' THEN 'VALUE'
                    WHEN 'P' THEN 'PATH'
                    WHEN 'R' THEN 'PROPERTY'
                ELSE '' 
                END
            END + ' WITH (Pad_Index = ' +
                CASE WHEN xi.[is_padded] = 1 
                THEN 'ON);'
                ELSE 'OFF);'
                END
    ELSE
        CASE WHEN i.is_primary_key = 1 THEN
            'ALTER TABLE ' + QUOTENAME(Schema_Name(t.[schema_id])) + '.' + QUOTENAME(t.[name]) +
            ' WITH NOCHECK ADD CONSTRAINT ' + QUOTENAME(i.[name]) +
            ' PRIMARY KEY ' + COALESCE(i.[type_desc],'') + '(' +
            COALESCE(STUFF( 
                (SELECT ',' + QUOTENAME(c.[name]) + 
                        CASE WHEN ic.[is_descending_key] = 1 
                        THEN ' DESC'
                        ELSE ' ASC' 
                        END
                 FROM sys.index_columns ic
                 INNER JOIN sys.columns c
                    ON  ic.[column_id] = c.[column_id] AND    
                        ic.[object_id] = c.[object_id]
                 WHERE i.[index_id] = ic.[index_id] AND
                       i.[object_id] = ic.[object_id] AND
                       ic.[is_included_column] = 0
                 ORDER BY ic.[key_ordinal]
                 FOR xml PATH(''))
            ,1,1,''),'') COLLATE DATABASE_DEFAULT + ')' +
            CASE WHEN EXISTS (SELECT 1 
                              FROM sys.[index_columns] ic2 
                              WHERE ic2.[index_id] = i.[index_id] AND 
                                    ic2.[object_id] = i.[object_id] AND
                                    ic2.[is_included_column] = 1)
            THEN ' INCLUDE (' +
                 COALESCE(STUFF( 
                    (SELECT ',' + QUOTENAME(c.[name])
                     FROM sys.index_columns ic
                     INNER JOIN sys.columns c
                        ON  ic.[column_id] = c.[column_id] AND    
                            ic.[object_id] = c.[object_id]
                     WHERE i.[index_id] = ic.[index_id] AND
                           i.[object_id] = ic.[object_id] AND
                           ic.[is_included_column] = 1
                     ORDER BY ic.[key_ordinal]
                     FOR xml PATH(''))
                 ,1,1,''),'') COLLATE DATABASE_DEFAULT 
            ELSE ''
            END +
            CASE WHEN i.is_padded = 1 THEN 
                ' WITH PAD INDEX ON ' + 
                CASE WHEN @Move_NCL_Idx_FG IS NOT NULL AND i.[type] = 2
                THEN QUOTENAME(@Move_NCL_Idx_FG) + ';'
                ELSE QUOTENAME(@Dest_FG) + ';'
                END
            ELSE ' ON ' + 
                CASE WHEN @Move_NCL_Idx_FG IS NOT NULL AND i.[type] = 2
                THEN QUOTENAME(@Move_NCL_Idx_FG) + ';'
                ELSE QUOTENAME(@Dest_FG) + ';'
                END
            END
        WHEN i.is_unique_constraint = 1 THEN
            'ALTER TABLE ' + QUOTENAME(Schema_Name(t.[schema_id])) + '.' + QUOTENAME(t.[name]) +
            ' WITH NOCHECK ADD CONSTRAINT ' + QUOTENAME(i.[name]) +
            ' UNIQUE ' + i.[type_desc] + ' (' +
            COALESCE(STUFF( 
                (SELECT ',' + QUOTENAME(c.[name]) + 
                        CASE WHEN ic.[is_descending_key] = 1 
                        THEN ' DESC'
                        ELSE ' ASC' 
                        END
                 FROM sys.index_columns ic
                 INNER JOIN sys.columns c
                    ON  ic.[column_id] = c.[column_id] AND    
                        ic.[object_id] = c.[object_id]
                 WHERE i.[index_id] = ic.[index_id] AND
                       i.[object_id] = ic.[object_id] AND
                       ic.[is_included_column] = 0
                 ORDER BY ic.[key_ordinal]
                 FOR xml PATH(''))
            ,1,1,''),'') COLLATE DATABASE_DEFAULT + ')' +
            CASE WHEN EXISTS (SELECT 1 
                              FROM sys.[index_columns] ic2 
                              WHERE ic2.[index_id] = i.[index_id] AND 
                                    ic2.[object_id] = i.[object_id] AND
                                    ic2.[is_included_column] = 1)
            THEN ' INCLUDE (' +
                 COALESCE(STUFF( 
                    (SELECT ',' + QUOTENAME(c.[name])
                     FROM sys.index_columns ic
                     INNER JOIN sys.columns c
                        ON  ic.[column_id] = c.[column_id] AND    
                            ic.[object_id] = c.[object_id]
                     WHERE i.[index_id] = ic.[index_id] AND
                           i.[object_id] = ic.[object_id] AND
                           ic.[is_included_column] = 1
                     ORDER BY ic.[key_ordinal]
                     FOR xml PATH(''))
                 ,1,1,''),'') COLLATE DATABASE_DEFAULT 
            ELSE ''
            END  +
            CASE WHEN i.is_padded = 1 THEN 
                ' WITH PAD INDEX ON '  + 
                CASE WHEN @Move_NCL_Idx_FG IS NOT NULL AND i.[type] = 2
                THEN QUOTENAME(@Move_NCL_Idx_FG) + ';'
                ELSE QUOTENAME(@Dest_FG) + ';'
                END
            ELSE ' ON '  + 
                CASE WHEN @Move_NCL_Idx_FG IS NOT NULL AND i.[type] = 2
                THEN QUOTENAME(@Move_NCL_Idx_FG) + ';'
                ELSE QUOTENAME(@Dest_FG) + ';'
                END
            END
        WHEN i.is_unique = 1 THEN
            'CREATE UNIQUE ' + i.[type_desc] + ' INDEX ' + 
            QUOTENAME(i.[name]) + 
            ' ON ' + QUOTENAME(Schema_Name(t.[schema_id])) + '.' + QUOTENAME(t.[name]) + ' (' +
            COALESCE(STUFF( 
                (SELECT ',' + QUOTENAME(c.[name]) + 
                        CASE WHEN ic.[is_descending_key] = 1 
                        THEN ' DESC'
                        ELSE ' ASC' 
                        END
                 FROM sys.index_columns ic
                 INNER JOIN sys.columns c
                    ON  ic.[column_id] = c.[column_id] AND    
                        ic.[object_id] = c.[object_id]
                 WHERE i.[index_id] = ic.[index_id] AND
                       i.[object_id] = ic.[object_id] AND
                       ic.[is_included_column] = 0
                 ORDER BY ic.[key_ordinal]
                 FOR xml PATH(''))
            ,1,1,''),'') COLLATE DATABASE_DEFAULT + ')' +
            CASE WHEN EXISTS (SELECT 1 
                              FROM sys.[index_columns] ic2 
                              WHERE ic2.[index_id] = i.[index_id] AND 
                                    ic2.[object_id] = i.[object_id] AND
                                    ic2.[is_included_column] = 1)
            THEN ' INCLUDE (' +
                 COALESCE(STUFF( 
                    (SELECT ',' + QUOTENAME(c.[name])
                     FROM sys.index_columns ic
                     INNER JOIN sys.columns c
                        ON  ic.[column_id] = c.[column_id] AND    
                            ic.[object_id] = c.[object_id]
                     WHERE i.[index_id] = ic.[index_id] AND
                           i.[object_id] = ic.[object_id] AND
                           ic.[is_included_column] = 1
                     ORDER BY ic.[key_ordinal]
                     FOR xml PATH(''))
                 ,1,1,''),'') COLLATE DATABASE_DEFAULT + ');'
            ELSE ''
            END +
            CASE WHEN i.is_padded = 1 THEN 
                ' WITH PAD INDEX ON '  + 
                CASE WHEN @Move_NCL_Idx_FG IS NOT NULL AND i.[type] = 2
                THEN QUOTENAME(@Move_NCL_Idx_FG) + ';'
                ELSE QUOTENAME(@Dest_FG) + ';'
                END
            ELSE ' ON '  + 
                CASE WHEN @Move_NCL_Idx_FG IS NOT NULL AND i.[type] = 2
                THEN QUOTENAME(@Move_NCL_Idx_FG) + ';'
                ELSE QUOTENAME(@Dest_FG) + ';'
                END
            END
        ELSE
            'CREATE ' + i.[type_desc] + ' INDEX ' + 
            QUOTENAME(i.[name]) + 
            ' ON ' + QUOTENAME(Schema_Name(t.[schema_id])) + '.' + QUOTENAME(t.[name]) + ' (' +
            COALESCE(STUFF( 
                (SELECT ',' + QUOTENAME(c.[name]) + 
                        CASE WHEN ic.[is_descending_key] = 1 
                        THEN ' DESC'
                        ELSE ' ASC' 
                        END
                 FROM sys.index_columns ic
                 INNER JOIN sys.columns c
                    ON  ic.[column_id] = c.[column_id] AND    
                        ic.[object_id] = c.[object_id]
                 WHERE i.[index_id] = ic.[index_id] AND
                       i.[object_id] = ic.[object_id] AND
                       ic.[is_included_column] = 0
                 ORDER BY ic.[key_ordinal]
                 FOR xml PATH(''))
            ,1,1,''),'') COLLATE DATABASE_DEFAULT + ')' +
            CASE WHEN EXISTS (SELECT 1 
                              FROM sys.[index_columns] ic2 
                              WHERE ic2.[index_id] = i.[index_id] AND 
                                    ic2.[object_id] = i.[object_id] AND
                                    ic2.[is_included_column] = 1)
            THEN ' INCLUDE (' +
                 COALESCE(STUFF( 
                    (SELECT ',' + QUOTENAME(c.[name])
                     FROM sys.index_columns ic
                     INNER JOIN sys.columns c
                        ON  ic.[column_id] = c.[column_id] AND    
                            ic.[object_id] = c.[object_id]
                     WHERE i.[index_id] = ic.[index_id] AND
                           i.[object_id] = ic.[object_id] AND
                           ic.[is_included_column] = 1
                     ORDER BY ic.[key_ordinal]
                     FOR xml PATH(''))
                 ,1,1,''),'') COLLATE DATABASE_DEFAULT + ');'
            ELSE ''
            END +
            CASE WHEN i.is_padded = 1 THEN 
                ' WITH PAD INDEX ON '  + 
                CASE WHEN @Move_NCL_Idx_FG IS NOT NULL AND i.[type] = 2
                THEN QUOTENAME(@Move_NCL_Idx_FG) + ';'
                ELSE QUOTENAME(@Dest_FG) + ';'
                END
            ELSE ' ON '  + 
                CASE WHEN @Move_NCL_Idx_FG IS NOT NULL AND i.[type] = 2
                THEN QUOTENAME(@Move_NCL_Idx_FG) + ';'
                ELSE QUOTENAME(@Dest_FG) + ';'
                END
            END
        END 
    END AS CreateIdxScript,
    --************* CREATE "DROP" XML INDEX SCRIPT ******************
    CASE WHEN i.[type] = 3 THEN
        'DROP INDEX ' + QUOTENAME(xi.Name) + ' ON ' +
        QUOTENAME(SCHEMA_NAME(t.schema_id)) +     '.' +
        QUOTENAME(t.name)
    ELSE
        CASE WHEN i.[is_primary_key] = 1 OR i.[is_unique_constraint] = 1 THEN
            'ALTER TABLE ' + QUOTENAME(Schema_Name(t.[schema_id])) + '.' + QUOTENAME(t.[name]) +
            ' DROP CONSTRAINT ' + QUOTENAME(i.[name])  + ';'
        ELSE
            'DROP INDEX ' + QUOTENAME(i.[name]) + ' ON ' +
            + QUOTENAME(Schema_Name(t.[schema_id])) + '.' + QUOTENAME(t.[name]) + ';'
        END
    END AS DropIdxScript,
    CASE 
    WHEN i.[type] IN(0,1) THEN 1
    WHEN i.[type] = 2 THEN 2
    WHEN i.[type] = 3 THEN
        CASE WHEN xi.[secondary_type] IS NULL 
        THEN 3 --Primary XML index
        ELSE 4 --Secondary XML index
        END
    --WHEN i.[type] = 4 THEN 5 --I am not generating spatial indexes
    ELSE 6
    END AS [CreateSeq],
    CASE 
    WHEN i.[type] IN(0,1) THEN 6
    WHEN i.[type] = 2 THEN 5
    WHEN i.[type] = 3 THEN
        CASE WHEN xi.[secondary_type] IS NULL 
        THEN 4 --Primary XML index
        ELSE 3 --Secondary XML index
        END
    --WHEN i.[type] = 4 THEN 2 --I am not generating spatial indexes
    ELSE 1
    END AS [DropSeq]
FROM sys.tables t
INNER JOIN sys.[indexes] i
    ON t.[object_id] = i.[object_id]
LEFT JOIN sys.[xml_indexes] xi
    ON xi.[object_id] = i.[object_id] AND
       xi.[index_id] = i.[index_id]
LEFT JOIN sys.xml_indexes AS PRI --primary XML Index info
    ON xi.object_id = PRI.object_id AND
       xi.using_xml_index_id = PRI.index_id
LEFT JOIN sys.[index_columns] ic
    ON xi.object_id = ic.Object_ID AND
       xi.Index_id = ic.Index_id
WHERE
    t.[is_ms_shipped] = 0 AND 
    t.[type] = 'U' 
GROUP BY
    t.[object_id],
    t.[name],
    t.[schema_id],
    i.[name],
    i.[type_desc],
    i.[object_id],
    i.[index_id],
    i.[is_primary_key],
    i.[is_padded],
    i.[is_unique],
    i.[is_unique_constraint],
    i.[is_hypothetical],
    i.[is_disabled],
    i.[type],
    i.[data_space_id],
    xi.[name],
    xi.[secondary_type],
    xi.[is_padded],
    ic.[column_id],
    Pri.[name]
)
GO

Sample Execution:

SELECT *
FROM [dbo].[fn_CreateIndexScripts](DEFAULT,DEFAULT)
WHERE
    IndexType IN(1,2,3)

Results:

There you go. A script that will generate indexes in the same or differing filegroups.  There are a lot of different uses for a script like this. I have a script in mind that will use this code to move tables and indexes to a different filegroup programmatically. 

More to come.. stay tuned.