create proc p_helpindex
@tbname sysname ='' ,
@CLUSTERED int = '1'
as
--生成索引信息及索引创建脚本
--author : happyflystone
--         Http://blog.csdn.net/happyflystone
--@tbname 表名，空返回空
--@CLUSTERED   是否显示聚集索引,1显示聚集索引,2不显示聚集索引
--调用：p_helpindex 'dbo.customers','1'
--转载请注明出处
if @tbname is null or @tbname = ''
    return -1
declare @t table(
    table_name nvarchar(100),
    schema_name nvarchar(100),
    fill_factor int,
    is_padded int,
    ix_name nvarchar(100),
    type int,
    keyno int,

.....................

..
--test
create table tb_test(id int,dept varchar(20))
go
CREATE UNIQUE CLUSTERED INDEX idx_6 ON tb_test(id) ON [PRIMARY] 
CREATE NONCLUSTERED INDEX idx_1 ON tb_test(id) ON [PRIMARY]
CREATE NONCLUSTERED INDEX idx_2 ON tb_test(id) WITH PAD_INDEX,FILLFACTOR =90 ON [PRIMARY]
CREATE NONCLUSTERED INDEX idx_3 ON tb_test(id) WITH FILLFACTOR =90 ON [PRIMARY]
CREATE NONCLUSTERED INDEX idx_4 ON tb_test(id,dept) ON [PRIMARY]
CREATE NONCLUSTERED INDEX idx_5 ON tb_test(dept,id) ON [PRIMARY]
CREATE NONCLUSTERED INDEX idx_7 ON tb_test(id,dept desc ) ON [PRIMARY]
CREATE NONCLUSTERED INDEX idx_8 ON tb_test(id desc ,dept) ON [PRIMARY]
go

exec p_helpindex 'tb_test'
drop proc p_helpindex
drop table tb_test

/*

col
-----------------------------------------------------
CREATE UNIQUE CLUSTERED INDEX idx_6 ON tb_test(id) ON [PRIMARY]
CREATE NONCLUSTERED INDEX idx_1 ON tb_test(id) ON [PRIMARY]
CREATE NONCLUSTERED INDEX idx_2 ON tb_test(id) WITH FILLFACTOR =90 ON [PRIMARY]
CREATE NONCLUSTERED INDEX idx_3 ON tb_test(id) WITH FILLFACTOR =90 ON [PRIMARY]
CREATE NONCLUSTERED INDEX idx_4 ON tb_test(id,dept) ON [PRIMARY]
CREATE NONCLUSTERED INDEX idx_5 ON tb_test(dept,id) ON [PRIMARY]
CREATE NONCLUSTERED INDEX idx_7 ON tb_test(id,dept desc ) ON [PRIMARY]
CREATE NONCLUSTERED INDEX idx_8 ON tb_test(id desc ,dept) ON [PRIMARY]

*/