Internal query processor for query to script an index by Erland Sommarskog

Sorry connect kills line break formatting, you'll have to re-guess them.
(1) result lacks a closing parenthesis anyway <grin/>
(2) pre-converting the schemaless XML column of the ic2 part to nvarchar works around the hitch (at least in 10.5.4000)
(3) I'm not surprised...




IF OBJECT_ID(N'dbo.aba_onoff',N'FN') IS NULL
    EXEC(N'CREATE FUNCTION dbo.aba_onoff(@bit bit) RETURNS varchar(3) AS BEGIN RETURN (CASE @bit WHEN 1 THEN ''ON'' WHEN 0 THEN ''OFF'' END) END');

declare @ixcmd nvarchar(MAX), @ixname sysname, @tblname sysname, @object_id int;
SELECT TOP 1
    @ixname = i.name
    , @tblname = t.name
    , @object_id = t.object_id
FROM sys.tables t
INNER JOIN sys.indexes i
    ON i.object_id = t.object_id;

SELECT @ixcmd = 'CREATE '
    + CASE i.is_unique WHEN 1 THEN 'UNIQUE ' ELSE '' END
    + CASE i.index_id WHEN 1 THEN 'CLUSTERED ' ELSE '' END
    + 'INDEX ' + quotename(@ixname)
    + ' ON ' + quotename(@tblname)
    + '(' + substring(ic1.collist.value('.', 'nvarchar(MAX)'), 1, len(ic1.collist.value('.', 'nvarchar(MAX)')) - 1) + ')'
    + CASE WHEN ic2.incllist IS NOT NULL
        THEN ' INCLUDE(' + substring(ic2.incllist, 1, len(ic2.incllist) - 1) + ')'
        ELSE '' END
    + CASE WHEN i.filter_definition IS NOT NULL
        THEN 'WHERE ' + i.filter_definition
        ELSE '' END
    + 'WITH (IGNORE_DUP_KEY=' + dbo.aba_onoff(i.ignore_dup_key) + ')'
FROM sys.indexes i
CROSS APPLY (
    SELECT quotename(c.name)
        + CASE ic.is_descending_key WHEN 1 THEN ' DESC' ELSE '' END
        + ','
    FROM sys.index_columns ic
    JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
    WHERE ic.object_id = i.object_id AND ic.index_id = i.index_id AND ic.key_ordinal > 0
    ORDER BY ic.key_ordinal
    FOR XML PATH(''), TYPE
    ) AS ic1(collist)
OUTER APPLY (
    SELECT
        ic2a.incllist.value('.', 'nvarchar(MAX)')
    FROM (
    SELECT quotename(c.name) + ','
    FROM sys.index_columns ic
    JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
    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
    FOR XML PATH(''), TYPE
    ) ic2a(incllist)
    ) AS ic2(incllist)
WHERE object_id = @object_id AND name = @ixname;

PRINT @ixcmd;

Oops! I forgot to include the function aba_onoff. Here it is:

CREATE FUNCTION dbo.aba_onoff(@bit bit) RETURNS varchar(3) AS
BEGIN
RETURN (CASE @bit WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' END)
END