SQL Server 列出包含列(非键)的所有索引

llycmphe  于 2022-12-03  发布在  其他
关注(0)|答案(4)|浏览(185)

我已尝试sp_helpindex,但它显示的是具有索引的列,而不是包含的列。请告诉我如何列出具有包含的列(非键)的所有索引?

c6ubokkw

c6ubokkw1#

对目录视图尝试以下T-SQL查询:

SELECT 
    IndexName = i.Name,
    ColName = c.Name
FROM 
    sys.indexes i
INNER JOIN 
    sys.index_columns ic ON ic.object_id = i.object_id AND ic.index_id = i.index_id
INNER JOIN 
    sys.columns c ON c.object_id = ic.object_id AND c.column_id = ic.column_id
WHERE
    ic.is_included_column = 1
ORDER BY 
    i.Name

它基本上检查所有索引(sys.indexes),然后链接到它们的列,并检查哪些列被设计为包含列(ic.is_included_column = 1),然后列出所有这些索引和所有这些列。

ekqde3dh

ekqde3dh2#

http://www.midnightdba.com/Jen/2009/12/get-index-included-column-info/复制

SELECT 
 OBJECT_NAME(i.[object_id]) TableName ,
 i.[name] IndexName ,
 c.[name] ColumnName ,
 ic.is_included_column ,
 i.index_id ,
 i.type_desc ,
 i.is_unique ,
 i.data_space_id ,
 i.ignore_dup_key ,
 i.is_primary_key ,
 i.is_unique_constraint
FROM 
 sys.indexes i
 JOIN sys.index_columns ic ON ic.object_id = i.object_id and i.index_id = ic.index_id
 JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
ORDER BY 
 tableName ,
 ic.index_id ,
 ic.index_column_id
xlpyo6sf

xlpyo6sf3#

如果希望全局系统存储过程可用于获取索引键/非键(包括)列,请运行以下代码

USE master
GO

CREATE PROCEDURE sp_helpcolindex
    @objname NVARCHAR(776)
AS
BEGIN
    SELECT 
        c.Name,
        CASE ic.is_included_column WHEN 0 THEN 'Key' ELSE 'Include' END AS [Type]
    FROM sys.indexes i
    JOIN sys.index_columns ic ON ic.object_id = i.object_id AND ic.index_id = i.index_id
    JOIN sys.columns c ON c.object_id = ic.object_id AND c.column_id = ic.column_id
    WHERE i.name = @objname 
    ORDER BY [Type] DESC
END
GO

EXEC sp_ms_marksystemobject 'sp_helpcolindex' 
GO
sg24os4d

sg24os4d4#

我知道这是相当旧的,但是我已经创建了sp_helpindex的一个修改版本,它输出include列。

use master;

if object_id('dbo.sp_helpindex2') is not null
    drop procedure dbo.sp_helpindex2;
go

create procedure dbo.sp_helpindex2(
    @objname nvarchar(776)-- the table to check for indexes
)
as
begin
-- PRELIM

    set nocount on;

    declare
        @objid            int       -- the object id of the table
       ,@indid            smallint  -- the index id of an index
       ,@groupid          int       -- the filegroup id of an index
       ,@indname          sysname
       ,@groupname        sysname
       ,@status           int
       ,@keys             nvarchar(2126)    -- Length (16*max_identifierLength)+(15*2)+(16*3)
       ,@includes         nvarchar(2126)    -- Length (16*max_identifierLength)+(15*2)+(16*3)
       ,@dbname           sysname
       ,@ignore_dup_key   bit
       ,@is_unique        bit
       ,@is_hypothetical  bit
       ,@is_primary_key   bit
       ,@is_unique_key    bit
       ,@is_columnstore   bit
       ,@auto_created     bit
       ,@no_recompute     bit
       ,@memory_optimized bit   -- For hekaton tables
       ,@hash_index       bit;  -- The index is a hash index

    -- Check to see that the object names are local to the current database.
    select @dbname = parsename(@objname,3);
    if @dbname is null
        select @dbname = db_name();
    else if @dbname <> db_name()
    begin
        raiserror(15250,-1,-1);
        return (1)
    end;

    -- Check to see that the table exists and initialize @objid.
    select @objid = object_id(@objname);
    if @objid is null
    begin
        raiserror(15009,-1,-1,@objname,@dbname);
        return (1)
    end;

    -- OPEN CURSOR OVER INDEXES (skip stats: bug shiloh_51196)
    declare ms_crs_ind cursor local static
    for select
             i.index_id
            ,i.data_space_id
            ,i.name
            ,i.ignore_dup_key
            ,i.is_unique
            ,i.is_hypothetical
            ,i.is_primary_key
            ,i.is_unique_constraint
            ,case
                 when type = 5 or type = 6 then 1
                 else 0
             end
            ,case
                 when type = 5 or type = 6 then 0
                 else s.auto_created
             end
            ,case
                 when type = 5 or type = 6 then 0
                 else s.no_recompute
             end
            ,case
                 when type = 7 then 1
                 else 0
             end
        from sys.indexes as i
            left join sys.stats as s
                on i.object_id = s.object_id
                   and i.index_id = s.stats_id
        where i.object_id = @objid
              and type in ( 1,2,5,6,7 );

    open ms_crs_ind;

    fetch ms_crs_ind into
        @indid
       ,@groupid
       ,@indname
       ,@ignore_dup_key
       ,@is_unique
       ,@is_hypothetical
       ,@is_primary_key
       ,@is_unique_key
       ,@is_columnstore
       ,@auto_created
       ,@no_recompute
       ,@hash_index;

    -- IF NO INDEX, QUIT
    if @@fetch_status < 0
    begin
        deallocate ms_crs_ind;
        raiserror(15472,-1,-1,@objname); -- Object does not have any indexes.
        return (0)
    end;

    if (select object_id('tempdb..#spindtab')) is not null
        drop table #spindtab

    create table #spindtab
    (
        index_name       sysname collate catalog_default not null
       ,index_id         int
       ,ignore_dup_key   bit
       ,is_unique        bit
       ,is_hypothetical  bit
       ,is_primary_key   bit
       ,is_unique_key    bit
       ,is_columnstore   bit
       ,auto_created     bit
       ,no_recompute     bit
       ,memory_optimized bit
       ,hash_index       bit
       ,groupname        sysname collate catalog_default null
       ,index_keys       nvarchar(2126) collate catalog_default null -- see @keys above for length descr
       ,include_cols     nvarchar(2126) collate catalog_default null -- see @keys above for length descr
    );

    -- Now check out each index, figure out its type and keys and
    --    save the info in a temporary table that we'll print out at the end.
    while @@fetch_status >= 0
    begin
        -- First we'll figure out what the keys are.
        declare
            @i       int
           ,@thiskey nvarchar(131) -- 128+3
           ,@is_include_column bit;

        select
             @keys = ''
            ,@includes = '';

        declare jbo_crs_ind_cols cursor local static
        for select
                 c.name
                ,ic.is_included_column
            from sys.indexes as i
                inner join sys.index_columns as ic
                    on ic.index_id = i.index_id
                    and ic.object_id = i.object_id
                inner join sys.columns as c
                    on c.column_id = ic.column_id
                    and c.object_id = ic.object_id
            where i.object_id = @objid
                and i.index_id = @indid
            order by ic.is_included_column, ic.index_column_id;

        open jbo_crs_ind_cols;

        fetch jbo_crs_ind_cols
        into @thiskey, @is_include_column

        if @@fetch_status < 0
        begin
            deallocate jbo_crs_ind_cols;
            continue;
        end;

        while @@fetch_status >= 0
        begin
            if(@is_include_column = 0)
                select @keys = @keys + iif(len(@keys) > 0, ', ', '') + @thiskey
            else
                select @includes = @includes + iif(len(@includes) > 0, ', ', '') + @thiskey

            fetch jbo_crs_ind_cols
            into @thiskey, @is_include_column
        end

        deallocate jbo_crs_ind_cols;

        select @groupname = null;
        if serverproperty('EngineEdition') != 5
            select @groupname = name
            from sys.data_spaces
            where data_space_id = @groupid;

        select @memory_optimized = is_memory_optimized
        from sys.tables
        where object_id = @objid;

        -- INSERT ROW FOR INDEX
        insert into #spindtab
        values
        (
             @indname
            ,@indid
            ,@ignore_dup_key
            ,@is_unique
            ,@is_hypothetical
            ,@is_primary_key
            ,@is_unique_key
            ,@is_columnstore
            ,@auto_created
            ,@no_recompute
            ,@memory_optimized
            ,@hash_index
            ,@groupname
            ,@keys
            ,@includes
        );

        -- Next index
        fetch ms_crs_ind into
            @indid
           ,@groupid
           ,@indname
           ,@ignore_dup_key
           ,@is_unique
           ,@is_hypothetical
           ,@is_primary_key
           ,@is_unique_key
           ,@is_columnstore
           ,@auto_created
           ,@no_recompute
           ,@hash_index;
    end;

    deallocate ms_crs_ind;

    -- DISPLAY THE RESULTS
    select
         'index_name' = index_name
        ,'index_description' = convert(varchar(210), --bits 16 off, 1, 2, 16777216 on, located on group
                case
                    when index_id = 1 then 'clustered'
                    else 'nonclustered'
                end + 
                case
                    when hash_index = 1 then ' hash'
                    else ''
                end + 
                case
                    when ignore_dup_key <> 0 then ', ignore duplicate keys'
                    else ''
                end + 
                case
                    when is_unique <> 0 then ', unique'
                    else ''
                end + 
                case
                    when is_hypothetical <> 0 then ', hypothetical'
                    else ''
                end + 
                case
                    when is_primary_key <> 0 then ', primary key'
                    else ''
                end + 
                case
                    when is_unique_key <> 0 then ', unique key'
                    else ''
                end + 
                case
                    when is_columnstore <> 0 then ', columnstore'
                    else ''
                end + 
                case
                    when auto_created <> 0 then ', auto create'
                    else ''
                end + 
                case
                    when no_recompute <> 0 then ', stats no recompute'
                    else ''
                end + 
                case
                    when memory_optimized = 1 then ' located in MEMORY '
                    else ''
                end + 
                case
                when groupname is not null
                        and (memory_optimized = 0
                            or memory_optimized is null) then ' located on ' + groupname
                else ''
            end)
        ,'index_keys' = index_keys
        ,'include_cols' = include_cols
    from #spindtab
    order by
         index_name;

    return (0) -- sp_helpindex2
end

execute sp_ms_marksystemobject 'dbo.sp_helpindex2';

相关问题