写这个proc有两个原因:

写这个proc有两个原因:
1.是系统的sp_helpindex不能显示include列
2.在做发布数据库时，可以只发布数据，然后脚建索引的脚本生成T-sql，到订阅端去执行.

SQL code

   
   

    
    
    
     

    
    create
    
     
    
    proc
    
     p_helpindex 

    
    @tbname
    
     sysname 
    
    =
    
    ''
    
     ,
    
    @type
    
     
    
    char
    
    (
    
    1
    
    ) 
    
    =
    
     
    
    '
    
    1
    
    '
    
    

    
    as
    
    

    
    --
    
    生成索引信息及索引创建脚本 
    
    
--
    
    author : perfectaction
    
    
--
    
    @tbname 表名，空返回所有表索引
    
    
--
    
    @type   是否显示聚集索引,1显示聚集索引,2不显示聚集索引
    
    
--
    
    调用：p_helpindex 'dbo.customers','1'
    
    

    
    with
    
     t 
    
    as
    
     (

    
    select
    
     rank() 
    
    over
    
     (
    
    order
    
     
    
    by
    
     b.name,a.name,c.name) 
    
    as
    
     id,c.index_id, 
b.name 
    
    as
    
     schema_name,a.name 
    
    as
    
     table_name,c.fill_factor,c.is_padded,
c.name 
    
    as
    
     ix_name,c.type,e.name 
    
    as
    
     column_name,d.index_column_id,c.is_primary_key,
d.is_included_column,f.name 
    
    as
    
     
    
    filegroup_name
    
    ,c.is_unique,c.ignore_dup_key,
d.is_descending_key 
    
    as
    
     is_descending_key,c.allow_row_locks,c.allow_page_locks

    
    from
    
     sys.tables 
    
    as
    
     a

    
    inner
    
     
    
    join
    
     sys.schemas 
    
    as
    
     b 
    
    on
    
     a.schema_id
    
    =
    
    b.schema_id 
    
    and
    
     a.is_ms_shipped
    
    =
    
    0
    
    

    
    inner
    
     
    
    join
    
     sys.indexes 
    
    as
    
     c 
    
    on
    
     a.
    
    object_id
    
    =
    
    c.
    
    object_id
    
    

    
    inner
    
     
    
    join
    
     sys.index_columns 
    
    as
    
     d 
    
    on
    
     d.
    
    object_id
    
    =
    
    c.
    
    object_id
    
     
    
    and
    
     d.index_id
    
    =
    
    c.index_id

    
    inner
    
     
    
    join
    
     sys.columns 
    
    as
    
     e 
    
    on
    
     e.
    
    object_id
    
    =
    
    d.
    
    object_id
    
     
    
    and
    
     e.column_id
    
    =
    
    d.column_id

    
    inner
    
     
    
    join
    
     sys.data_spaces 
    
    as
    
     f 
    
    on
    
     f.data_space_id
    
    =
    
    c.data_space_id 

    
    where
    
     a.
    
    object_id
    
     
    
    like
    
     
    
    '
    
    %
    
    '
    
    +
    
    isnull
    
    (
    
    ltrim
    
    (
    
    object_id
    
    (
    
    @tbname
    
    )),
    
    ''
    
    )
    
    +
    
    '
    
    %
    
    '
    
    

    
    and
    
     c.is_hypothetical
    
    =
    
    0
    
     
    
    and
    
     is_disabled
    
    =
    
    0
    
     
    
    and
    
     c.type
    
    >=
    
    @type
    
    
)

    
    select
    
     
    
    distinct
    
     a.schema_name,a.table_name,a.ix_name,

    
    case
    
     a.type 
    
    when
    
     
    
    1
    
     
    
    then
    
     
    
    '
    
    clustered
    
    '
    
     
    
    when
    
     
    
    2
    
     
    
    then
    
     
    
    '
    
    nonclustered
    
    '
    
     
    
    else
    
     
    
    ''
    
     
    
    end
    
     
    
    as
    
     index_type,

    
    case
    
     a.is_primary_key 
    
    when
    
     
    
    0
    
     
    
    then
    
     
    
    '
    
    no
    
    '
    
     
    
    else
    
     
    
    '
    
    yes
    
    '
    
     
    
    end
    
     
    
    as
    
     is_primary_key,
m.ix_index_column_name,
    
    isnull
    
    (m.ix_index_include_column_name,
    
    ''
    
    ) 
    
    as
    
     ix_index_include_column_name,
a.
    
    filegroup_name
    
    ,
    
    replace
    
    (
    
    '
    
    create 
    
    '
    
    +
    
     
    
    case
    
     
    
    when
    
     is_unique
    
    =
    
    1
    
     
    
    then
    
     
    
    '
    
    unique 
    
    '
    
     
    
    else
    
     
    
    ''
    
     
    
    end
    
     

    
    +
    
     
    
    case
    
     
    
    when
    
     a.type
    
    =
    
    1
    
     
    
    then
    
     
    
    '
    
    clustered
    
    '
    
     
    
    else
    
     
    
    '
    
    nonclustered
    
    '
    
     
    
    end
    
      
    
    +
    
    '
    
     index 
    
    '
    
    

    
    +
    
     a.ix_name
    
    +
    
    '
    
     on 
    
    '
    
    +
    
    a.schema_name
    
    +
    
    '
    
    .
    
    '
    
    +
    
    a.table_name
    
    +
    
    '
    
    (
    
    '
    
    +
    
    m.ix_index_column_name
    
    +
    
    '
    
    )
    
    '
    
    

    
    +
    
     
    
    case
    
     
    
    when
    
     m.ix_index_include_column_name 
    
    is
    
     
    
    null
    
     
    
    then
    
     
    
    ''
    
     
    
    else
    
     
    
    '
    
    include(
    
    '
    
    +
    
    m.ix_index_include_column_name
    
    +
    
    '
    
    )
    
    '
    
    end
    
     

    
    +
    
     
    
    case
    
     
    
    when
    
     fill_factor
    
    >
    
    0
    
     
    
    or
    
     ignore_dup_key
    
    =
    
    1
    
     
    
    or
    
     is_padded
    
    =
    
    1
    
     
    
    or
    
     allow_row_locks
    
    =
    
    0
    
     
    
    or
    
     allow_page_locks
    
    =
    
    0
    
     
    
    then
    
     
    
    '
    
    with(
    
    '
    
     
    
    else
    
     
    
    ''
    
     
    
    end
    
    

    
    +
    
     
    
    case
    
     
    
    when
    
     fill_factor
    
    >
    
    0
    
     
    
    then
    
     
    
    '
    
    ,fillfactor=
    
    '
    
    +
    
    rtrim
    
    (fill_factor) 
    
    else
    
     
    
    ''
    
     
    
    end
    
     

    
    +
    
     
    
    case
    
     
    
    when
    
     is_padded
    
    =
    
    1
    
     
    
    then
    
     
    
    '
    
    ,pad_index=on
    
    '
    
     
    
    else
    
     
    
    ''
    
     
    
    end
    
     

    
    +
    
     
    
    case
    
     
    
    when
    
     ignore_dup_key
    
    =
    
    1
    
     
    
    then
    
     
    
    '
    
    ,ignore_dup_key=on
    
    '
    
     
    
    else
    
     
    
    ''
    
     
    
    end
    
     

    
    +
    
     
    
    case
    
     
    
    when
    
     allow_row_locks
    
    =
    
    0
    
     
    
    then
    
     
    
    '
    
    ,allow_row_locks=off
    
    '
    
     
    
    else
    
     
    
    ''
    
     
    
    end
    
    

    
    +
    
     
    
    case
    
     
    
    when
    
     allow_page_locks
    
    =
    
    0
    
     
    
    then
    
     
    
    '
    
    ,allow_page_locks=off
    
    '
    
     
    
    else
    
     
    
    ''
    
     
    
    end
    
    

    
    +
    
     
    
    case
    
     
    
    when
    
     fill_factor
    
    >
    
    0
    
     
    
    or
    
     ignore_dup_key
    
    =
    
    1
    
     
    
    or
    
     is_padded
    
    =
    
    1
    
     
    
    or
    
     allow_row_locks
    
    =
    
    0
    
     
    
    or
    
     allow_page_locks
    
    =
    
    0
    
     
    
    then
    
     
    
    '
    
    )
    
    '
    
     
    
    else
    
     
    
    ''
    
     
    
    end
    
    ,
    
    '
    
    with(,
    
    '
    
    ,
    
    '
    
    with(
    
    '
    
    )

    
    as
    
     sqlscript

    
    from
    
     t 
    
    as
    
     a

    
    outer
    
     apply  
(  
    
    
    select
    
     ix_index_column_name
    
    =
    
     
    
    stuff
    
    (
    
    replace
    
    (
    
    replace
    
    (  
            (  
                
    
    select
    
     
    
    case
    
     
    
    when
    
     b.is_descending_key 
    
    =
    
    1
    
     
    
    then
    
     column_name 
    
    +
    
     
    
    '
    
     desc
    
    '
    
     
    
    else
    
     column_name 
    
    end
    
     
    
    as
    
     column_name  
                
    
    from
    
     t 
    
    as
    
     b 
    
    where
    
     a.id
    
    =
    
    b.id 
    
    and
    
     is_included_column
    
    =
    
    0
    
     
    
    order
    
     
    
    by
    
     index_column_id 
    
    for
    
     xml auto  
            ), 
    
    '
    
    <b column_name="
    
    '
    
    , 
    
    '
    
    ,
    
    '
    
    ), 
    
    '
    
    "/>
    
    '
    
    , 
    
    ''
    
    ), 
    
    1
    
    , 
    
    1
    
    , 
    
    ''
    
    )  
            ,ix_index_include_column_name
    
    =
    
     
    
    stuff
    
    (
    
    replace
    
    (
    
    replace
    
    (  
            (  
                
    
    select
    
     column_name 
    
    from
    
     t 
    
    as
    
     b 
    
    where
    
     a.id
    
    =
    
    b.id 
    
    and
    
     is_included_column
    
    =
    
    1
    
      
                
    
    order
    
     
    
    by
    
     index_column_id 
    
    for
    
     xml auto  
            ), 
    
    '
    
    <e column_name="
    
    '
    
    , 
    
    '
    
    ,
    
    '
    
    ), 
    
    '
    
    "/>
    
    '
    
    , 
    
    ''
    
    ), 
    
    1
    
    , 
    
    1
    
    , 
    
    ''
    
    )  
)m  

    
    order
    
     
    
    by
    
     a.schema_name,a.table_name,a.ix_name

   
   

 

SQL code

   
   

    
    
    
    

    
    create
    
     
    
    database
    
     db_test

    
    go
    
    

    
    use
    
     db_test

    
    go
    
    


    
    create
    
     
    
    table
    
     tb(id 
    
    int
    
     
    
    primary
    
     
    
    key
    
    ,col_1 
    
    varchar
    
    (
    
    20
    
    ),col_2 
    
    varchar
    
    (
    
    30
    
    ),col_3 
    
    varchar
    
    (
    
    30
    
    ))

    
    go
    
    

    
    insert
    
     
    
    into
    
     tb 
    
    select
    
     
    
    1
    
    ,
    
    '
    
    a
    
    '
    
    ,
    
    '
    
    b
    
    '
    
    ,
    
    '
    
    c
    
    '
    
    

    
    go
    
    

    
    create
    
     
    
    index
    
     ix_01 
    
    on
    
     tb(col_1)

    
    create
    
     
    
    index
    
     ix_03 
    
    on
    
     tb(col_1,col_2 
    
    desc
    
    )

    
    create
    
     
    
    index
    
     ix_02 
    
    on
    
     tb(col_1)include(col_2)
    
    with
    
    (
    
    fillfactor
    
    =
    
    80
    
    ,pad_index
    
    =
    
    on
    
    )

    
    create
    
     
    
    unique
    
     
    
    index
    
     ix_04 
    
    on
    
     tb(col_1,col_3)include(col_2)
    
    with
    
    (ignore_dup_key
    
    =
    
    on
    
    )

    
    go
    
    


    
    --
    
    执行这个脚本的结果
    
    

    
    p_helpindex tb

    
    /*
    
    --生成的创建脚本
create nonclustered index ix_01 on dbo.tb(col_1)
create nonclustered index ix_02 on dbo.tb(col_1)include(col_2)with(fillfactor=80,pad_index=on)
create nonclustered index ix_03 on dbo.tb(col_1,col_2 desc)
create unique nonclustered index ix_04 on dbo.tb(col_1,col_3)include(col_2)with(ignore_dup_key=on)
create unique clustered index PK__tb__7C8480AE on dbo.tb(id)

    
    */