日期:2014-05-18  浏览次数:20643 次

Sql Server 2000生成索引信息及自动创建脚本



更多内容请看blog : 

http://blog.csdn.net/happyflystone/archive/2009/04/28/4131689.aspx


SQL code


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]

*/






------解决方案--------------------
up
------解决方案--------------------
頂~~
------解决方案--------------------
SF
------解决方案--------------------
學習.
------解决方案--------------------
学习
------解决方案--------------------
IO;
------解决方案--------------------
Mark
------解决方案--------------------
先收着
------解决方案--------------------
大小写不一.
------解决方案--------------------
asdfasdf
sdfasdfasdfas
df sdfasdf
asdf
asdf asdf

asdfsa fsd asd
------解决方案--------------------
這麽长。。收了。谢谢
------解决方案--------------------
UP~
------解决方案--------------------
不能簡單點碼?
------解决方案--------------------
留名!
------解决方案--------------------

------解决方案--------------------
长沙鼎卓人力资源管理公司教育部为社会下岗失业人员、转业军人、应、往届大学毕业生,为想学一技之长的青年免费提供电工、焊工培训,报名地址:五一广场平和堂对面锦绣中环28楼、联系电话:0731-4440515,联系人:赵老师