Skip to content

Instantly share code, notes, and snippets.

@raymondtlin

raymondtlin/get_idx.sql

Last active July 11, 2018 15:59
Show Gist options
  • Save raymondtlin/6b91c213d3a3a1eabbd30402eb920b11 to your computer and use it in GitHub Desktop.
Save raymondtlin/6b91c213d3a3a1eabbd30402eb920b11 to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. raymondtlin renamed this gist Jul 11, 2018. 1 changed file with 0 additions and 0 deletions.
    0 get_idx → get_idx.sql
    View file
    File renamed without changes.
  2. raymondtlin created this gist Jul 11, 2018.
    58 changes: 58 additions & 0 deletions get_idx
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,58 @@
    DECLARE @tbl VARCHAR(255);

    SET @tbl = 'charges'

    SELECT
    o.name
    , i.index_id
    , i.name
    , i.type_desc
    , SUBSTRING(ikey.cols, 3, LEN(ikey.cols)) AS key_cols
    , SUBSTRING(inc.cols, 3, LEN(inc.cols)) AS included_cols
    , STATS_DATE(o.object_id, i.index_id) AS stats_date
    , i.filter_definition
    FROM
    sys.objects o
    JOIN sys.indexes i
    ON i.object_id = o.object_id
    CROSS APPLY (
    SELECT
    ', ' + 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.is_included_column = 0
    ORDER BY
    ic.key_ordinal
    FOR
    XML PATH('') ) AS ikey ( cols )
    OUTER APPLY (
    SELECT
    ', ' + 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('') ) AS inc ( cols )
    WHERE
    o.name = @tbl
    AND i.type IN ( 1, 2 )
    ORDER BY
    o.name
    , i.index_id;