壹次网首页 | 工具源码首页 | 技术文章首页 | 文章分类 | | |

文章搜索： 分类 关键字
您的位置：首页 → 数 据 库 → MS SQL → SQL server 2000全自动化多功能的索引重建通用代码
SQL server 2000全自动化多功能的索引重建通用代码
添加日期：2007-10-7 18:59:07     [大 中 小]
文章简介：
   索引重建的重要性不言而喻，但是很奇怪，许是孤陋寡闻的缘故，却总也未曾见到有通用的代码以方便于一般用户的操作，更兼得前几日又有人问到这个问题，于是，经过断断续续的细化修改，一段不算长但是自认为设计思想相当优秀的通用代码终于调试完毕了。 文章正文：
    相信无论是熟悉还是不熟悉SQL Server的人都知道索引重建的重要性，我以前从来都没有想过这是当做一个问题存在，但是很奇怪，前几日又有人问到了这个问题，我意识到我以前的想法可能存在一定的偏差，于是，我就想整理一段适合普通用户使用的通用代码，最好是全自动化的，不需要用户任何手工干预的，呵，或许是这个解决方案的思路本来就很简单，居然很快就完成了，居然没费什么周折，不过调试的时候居然发现了我以往代码里普遍存在的一个漏洞，也算是有点收获。
    我们都知道索引重建使用最频繁的语句就是DBCC DBREINDEX命令语句，但是这条语句正常情况下，一次只能重建一个表，如果数据库的表比较少还好，如果数据库的表比较多的话，那么一个表一个表地重建，的确是一件很痛苦的事情，由此，自然想到了动态组装查询语句，利用动态组装查询语句从一个临时表中循环取值，那么，如何确定数据库中有那些用户表呢？这个问题很简单，系统表嘛，所以，这个解决方案的思路很简单，先从系统表中取出用户表列表存储起来，之后在循环语句中再利用动态组装语句组装出索引重建语句，达到一键重建当前数据库所有用户表索引的目的。当然，这个时候你应该多一个心眼，在进入循环之前处理掉那些没有记录的空表，空表重建索引毕竟毫无意义嘛。
下面我们就详细来分析这段全自动化的多功能的代码：
IF EXISTS (SELECT name
           FROM sysobjects
           WHERE name=N'teptable'
           AND Type='U')
    DROP TABLE teptable
GO
CREATE TABLE teptable(tepid smallint identity(1,1),
                       tablename varchar(116))
GO
INSERT INTO teptable(tablename)
SELECT name
FROM sysobjects
WHERE (type = 'u') AND (xtype = 'u')
and name not in('dtproperties','teptable')  
GO
IF EXISTS (SELECT name
           FROM sysobjects
           WHERE name=N'teptable01'
           AND Type='U')
    DROP TABLE teptable01
GO
CREATE TABLE teptable01(tepid smallint identity(1,1),
                       tablename varchar(116),
                       columnrows int)
GO
/*以上这些没什么好说的，都是比较常规的语句，需要提醒的是，如果你的数据库存在teptable和teotable01这2个表的话，那么，原始数据库表将被删除，因此，你可以先确认你的数据库是否存在这2个表，如果存在，则应该将本代码中的这2个表名替换为一个你当前数据库中不存在的表
*/
DECLARE @introws int,    ---表的记录数量
        @chvtablename varchar(116),
        @intvalue int,
        @chvquery Nvarchar(4000),  --组成的查询语句的结果
        @chvdbname char(116),  --数据库
        @chvusers char(116)    --对象的所有者
SELECT @chvdbname=db_name()
SELECT @intvalue=min(tepid)
FROM  teptable

WHILE @intvalue is not null
BEGIN
SELECT @chvtablename=tablename
FROM teptable
WHERE [email=tepid=@intvalue]tepid=@intvalue[/email]
SELECT @chvusers=sysusers.name
FROM sysusers join sysobjects
ON sysusers.uid=sysobjects.uid
WHERE [email=sysobjects.name=@chvtablename]sysobjects.name=@chvtablename[/email]
--取得相应对象的所有者，因为不可能在所有的数据库中任何对象的所有者都是dbo。

SET @chvquery=N'select @introws =count(*)'+' from ['+RTrim(@chvdbname)+'].['+ RTrim(@chvusers)+'].['+RTrim(@chvtablename)+']'
--动态组装查询语句，为了防止出现漏洞，应该把对象名称都括起来。
EXEC sp_executesql @chvquery,N'@introws Int Output',@introws Output
--执行组装而成的语句，并将表的行数输出，因为T-SQL语言不能自定义全局变量

IF @introws<>0
  INSERT teptable01(tablename,columnrows)
     VALUES(@chvtablename,@introws)
/*将记录数量不是0的表插入到teptable01表中，为动态组装的查询语句做准备，在这里，比将所有的表都插入到teptable01表中再删除行数为0的表的想法要好的多*/

--PRINT @chvquery
--如果不注释掉上面的这条调试语句，你可以看到所有组装而成的查询的实际的查询语句

SELECT @intvalue=min(tepid)
FROM teptable
WHERE tepid>@intvalue
--继续循环
END
GO
DECLARE @introws1 int,
        @chvtablename1 varchar(116),
        @intvalue1 int,
        @chvquery1 Nvarchar(4000),
        @chvusers1 char(50),
        @chvdbname1 char(116),
        @interror  int,  --错误计数器
        @intright int,   --正确计数器
        @intisnullnum int,--空表的数量
        @introwscount bigint,--记录的总数量
        @introwsnum int,--表的总数量
        @intisnonullnum int—非空表的总数量

SELECT @interror=0
SELECT @intright=0
--给2个计数器赋值，初始值都是0。

SELECT @introwsnum=count(*)
FROM teptable

SELECT @intisnonullnum=count(*)
FROM teptable01

SELECT @intisnullnum=@introwsnum-@intisnonullnum
--以上的语句就是计算有多少个空表，没什么可说的
SELECT @introwscount=sum(columnrows)
FROM teptable01
--计算数据库的记录总数量

SELECT @chvdbname1=db_name()

SELECT @intvalue1=min(tepid)
FROM  teptable01

WHILE @intvalue1 is not null
BEGIN

SELECT @chvtablename1=tablename
FROM teptable01
WHERE [email=tepid=@intvalue1]tepid=@intvalue1[/email]

SELECT @chvusers1=sysusers.name
FROM sysusers join sysobjects
ON sysusers.uid=sysobjects.uid
WHERE [email=sysobjects.name=@chvtablename1]sysobjects.name=@chvtablename1[/email]

SET @chvquery1=N'DBCC DBREINDEX(''['+RTrim(@chvdbname1)+'].['+RTrim(@chvusers1)+'].['+RTrim(@chvtablename1)+']'','+''''''+',80)'
/*里，要注意到一个动态组装写法的技巧，那就是实际语句中单引号在动态组装要换成双引号，动态组装的各部分用单引号括起来，需要使用变量的值的变量不要括，当做字符串使用的变量要括起来。你可以先写出实际的句子，对照一下与这里的区别。掌握动态组装写法的技巧。
*/
EXEC sp_executesql @chvquery1
IF @@error<>0
  SELECT @interror = @interror + 1
--如果出错，计数器自动加1。
IF @@error=0
   SELECT @intright= @intright +1
--如果正确，计数器自动加1。
--PRINT @chvquery1
--如果不注释掉上面的这条调试语句，你可以看到所有组装而成的查询的实际的查询语句

SELECT @intvalue1=min(tepid)
FROM teptable01
WHERE tepid>@intvalue1
END
IF @interror=0
   PRINT '梅兰竹菊恭喜你，索引重建顺利执行完毕，一共执行用户表索引重建'+LTrim(str(@intright))+'个。当前数据库的规格：'+LTrim(str(@introwsnum))+'个用户表,空表共'+LTrim(Str(@intisnullnum))+'个，本数据库当前记录数量：'+LTrim(str(@introwscount))+'条。'
--如果执行没有任何差错，将告诉你有关当前数据库的规格

ELSE                                         
   PRINT '很遗憾，出现了'+LTrim(str(@interror))+'处错误，请仔细检查，并联系你的系统管理员'
GO

DECLARE @interror int
SELECT @interror=0

DROP TABLE teptable
IF @@error <>0
SELECT @interror=@interror+1

DROP TABLE teptable01
IF @@error <>0
SELECT @interror=@interror+1

IF @interror=0
   PRINT '梅兰竹菊恭喜你，索引重建临时用表清理顺利执行完毕。'
ELSE
   PRINT '很遗憾，出现了'+LTrim(str(@interror))+'处错误，请仔细检查，并联系你的系统管理员'
GO

呵，这段程序比较简单，但是很实用，完整的代码如下：
IF EXISTS (SELECT name
           FROM sysobjects
           WHERE name=N'teptable'
           AND Type='U')
    DROP TABLE teptable
GO

CREATE TABLE teptable(tepid smallint identity(1,1),
                       tablename varchar(116) )
GO
INSERT INTO teptable(tablename)
SELECT name
FROM sysobjects
WHERE (type = 'u') AND (xtype = 'u')
and name not in('dtproperties','teptable')
GO
IF EXISTS (SELECT name
           FROM sysobjects
           WHERE name=N'teptable01'
           AND Type='U')
    DROP TABLE teptable01
GO

CREATE TABLE teptable01(tepid smallint identity(1,1),
                       tablename varchar(116),
                       columnrows int)
GO

DECLARE @introws int,
        @chvtablename varchar(116),
        @intvalue int,
        @chvquery Nvarchar(4000),
        @chvdbname char(116),
        @chvusers char(116)

SELECT @chvdbname=db_name()

SELECT @intvalue=min(tepid)
FROM  teptable

WHILE @intvalue is not null
BEGIN

SELECT @chvtablename=tablename
FROM teptable
WHERE [email=tepid=@intvalue]tepid=@intvalue[/email]


SELECT @chvusers=sysusers.name
FROM sysusers join sysobjects
ON sysusers.uid=sysobjects.uid
WHERE [email=sysobjects.name=@chvtablename]sysobjects.name=@chvtablename[/email]

SET @chvquery=N'select @introws =count(*)'+' from ['+RTrim(@chvdbname)+'].['+ RTrim(@chvusers)+'].['+RTrim(@chvtablename)+']'
EXEC sp_executesql @chvquery,N'@introws Int Output',@introws Output

IF @introws<>0
  INSERT teptable01(tablename,columnrows)
     VALUES(@chvtablename,@introws)
--PRINT @chvquery
SELECT @intvalue=min(tepid)
FROM teptable
WHERE tepid>@intvalue
END
GO
DECLARE @introws1 int,
        @chvtablename1 varchar(116),
        @intvalue1 int,
        @chvquery1 Nvarchar(4000),
        @chvusers1 char(50),
        @chvdbname1 char(116),
        @interror  int,
        @intright int,
        @intisnullnum int,
        @introwscount bigint,
        @introwsnum int,
        @intisnonullnum int

SELECT @interror=0
SELECT @intright=0

SELECT @introwsnum=count(*)
FROM teptable

SELECT @intisnonullnum=count(*)
FROM teptable01

SELECT @intisnullnum=@introwsnum-@intisnonullnum

SELECT @introwscount=sum(columnrows)
FROM teptable01

SELECT @chvdbname1=db_name()

SELECT @intvalue1=min(tepid)
FROM  teptable01

WHILE @intvalue1 is not null
BEGIN

SELECT @chvtablename1=tablename
FROM teptable01
WHERE [email=tepid=@intvalue1]tepid=@intvalue1[/email]

SELECT @chvusers1=sysusers.name
FROM sysusers join sysobjects
ON sysusers.uid=sysobjects.uid
WHERE [email=sysobjects.name=@chvtablename1]sysobjects.name=@chvtablename1[/email]

SET @chvquery1=N'DBCC DBREINDEX(''['+RTrim(@chvdbname1)+'].['+RTrim(@chvusers1)+'].['+RTrim(@chvtablename1)+']'','+''''''+',80)'
EXEC sp_executesql @chvquery1

IF @@error<>0
  SELECT @interror = @interror + 1
IF @@error=0
   SELECT @intright= @intright +1
--PRINT @chvquery1
SELECT @intvalue1=min(tepid)
FROM teptable01
WHERE tepid>@intvalue1
END

IF @interror=0
   PRINT '梅兰竹菊恭喜你，索引重建顺利执行完毕，一共执行用户表索引重建'+LTrim(str(@intright))+'个。当前数据库的规格：'+LTrim(str(@introwsnum))+'个用户表,空表共'+LTrim(Str(@intisnullnum))+'个，本数据库当前记录数量：'+LTrim(str(@introwscount))+'条。'
ELSE                                         
   PRINT '很遗憾，出现了'+LTrim(str(@interror))+'处错误，请仔细检查，并联系你的系统管理员'
GO
DECLARE @interror int
SELECT @interror=0
    DROP TABLE teptable
IF @@error <>0
SELECT @interror=@interror+1
DROP TABLE teptable01
IF @@error <>0
SELECT @interror=@interror+1

IF @interror=0
   PRINT '梅兰竹菊恭喜你，索引重建临时用表清理顺利执行完毕。'
ELSE
   PRINT '很遗憾，出现了'+LTrim(str(@interror))+'处错误，请仔细检查，并联系你的系统管理员'
GO

原始的还有一个简化的版本，不包括数据库规格统计，执行速度要快那么一点点：
IF EXISTS (SELECT name
           FROM sysobjects
           WHERE name=N'teptable'
           AND Type='U')
    DROP TABLE teptable
GO
CREATE TABLE teptable(tepid smallint identity(1,1),
                       tablename varchar(116)
                       )
GO

INSERT INTO teptable(tablename)
SELECT name
FROM sysobjects
WHERE (type = 'u') AND (xtype = 'u')
and name not in('dtproperties','teptable')
GO

IF EXISTS (SELECT name
           FROM sysobjects
           WHERE name=N'teptable01'
           AND Type='U')
    DROP TABLE teptable01
GO

CREATE TABLE teptable01(tepid smallint identity(1,1),
                       tablename varchar(116),
                       columnrows int)
GO

DECLARE @introws int,
        @chvtablename varchar(116),
        @intvalue int,
        @chvquery Nvarchar(4000),
        @chvdbname char(116),
        @chvusers char(116)

SELECT @chvdbname=db_name()

SELECT @intvalue=min(tepid)
FROM  teptable

WHILE @intvalue is not null
BEGIN

SELECT @chvtablename=tablename
FROM teptable
WHERE [email=tepid=@intvalue]tepid=@intvalue[/email]


SELECT @chvusers=sysusers.name
FROM sysusers join sysobjects
ON sysusers.uid=sysobjects.uid
WHERE [email=sysobjects.name=@chvtablename]sysobjects.name=@chvtablename[/email]

SET @chvquery=N'select @introws =count(*)'+' from ['+RTrim(@chvdbname)+'].['+ RTrim(@chvusers)+'].['+RTrim(@chvtablename)+']'
EXEC sp_executesql @chvquery,N'@introws Int Output',@introws Output

IF @introws<>0
  INSERT teptable01(tablename,columnrows)
     VALUES(@chvtablename,@introws)

--PRINT @chvquery

SELECT @intvalue=min(tepid)
FROM teptable
WHERE tepid>@intvalue
END

GO

DECLARE @introws1 int,
        @chvtablename1 varchar(116),
        @intvalue1 int,
        @chvquery1 Nvarchar(4000),
        @chvusers1 char(50),
        @chvdbname1 char(116),
        @interror  int

SELECT @interror=0

SELECT @chvdbname1=db_name()

SELECT @intvalue1=min(tepid)
FROM  teptable01

WHILE @intvalue1 is not null
BEGIN

SELECT @chvtablename1=tablename
FROM teptable01
WHERE [email=tepid=@intvalue1]tepid=@intvalue1[/email]

SELECT @chvusers1=sysusers.name
FROM sysusers join sysobjects
ON sysusers.uid=sysobjects.uid
WHERE [email=sysobjects.name=@chvtablename1]sysobjects.name=@chvtablename1[/email]

SET @chvquery1=N'DBCC DBREINDEX(''['+RTrim(@chvdbname1)+'].['+RTrim(@chvusers1)+'].['+RTrim(@chvtablename1)+']'','+''''''+',80)'
EXEC sp_executesql @chvquery1

IF @@error<>0
  SELECT @interror = @interror + 1

--PRINT @chvquery1

SELECT @intvalue1=min(tepid)
FROM teptable01
WHERE tepid>@intvalue1
END

--PRINT @interror

IF @interror=0
   PRINT '梅兰竹菊恭喜你，索引重建顺利执行完毕'
ELSE
   PRINT '很遗憾，出现了'+RTrim(@interror)+'处错误，请仔细检查，并联系你的系统管理员'
GO

DECLARE @interror int

SELECT @interror=0

DROP TABLE teptable

IF @@error <>0
SELECT @interror=@interror+1

DROP TABLE teptable01

IF @@error <>0
SELECT @interror=@interror+1

IF @interror=0
   PRINT '梅兰竹菊恭喜你，索引重建临时用表清理顺利执行完毕'
ELSE
   PRINT '很遗憾，出现了'+RTrim(@interror)+'处错误，请仔细检查，并联系你的系统管理员'
GO

    好了，你打开查询分析器，选好要执行的数据库，之后再复制以上的简化的或者多功能的代码执行吧，呵，是否如你所愿？如果有什么不明白的地方，问我好了，MSN&E-Mail：yuyuxinqing@yahoo.com.cn，另希望有志于SQL Server的技术人员和对SQL Server有一定经验的技术人员加入QQ群18704593（刚刚建立的，以SQL Server技术为主），互相交流，共同提高！
出处：silverspider 作者：xwsoft 点击：
 热点文章
·photoshop制作香烟、烟头、烟雾
·photoshop图片卷角、翻页效果的制作
·用U盘或移动硬盘装《深山红叶袖珍系统Win PE启动..
·photoshop经典恐怖片海报效果教程
·ghost.pif最新变种手动查杀，ARP欺骗挂马＋KAV让..
·photoshop制作3D立体小人和字体
·photoshop水晶球质感表现 - 关于光影的细节
·关于zend解密的程序
·王晨昀：谁来拯救中小网站？
·photoshop泛黄、陈旧纸张效果的制作
 推荐文章
·如何彻底删除一个不需要的系统服务
·模仿QQ和MSN消息提示的效果
·CC攻击的思路及防范方法
·广告联盟的一些评价
Powered by yici.net
CopyRight (C) 2006-2012 版权所有   [ 依次网 ]；未经授权，任何人不得抄袭、仿冒本站
ICP备案：苏ICP备05011771号 证书下载 ICP证号：苏ICP证040301
仪征广目网络信息服务有限公司 版权所有