SQL Server 2000全自动化多功能的索引重建通用代码

时　间:2007-12-21 11:55:37
作　者:梅兰竹菊   ID：17  城市：秦皇岛
摘　要:索引重建的重要性不言而喻，但是很奇怪，许是孤陋寡闻的缘故，却总也未曾见到有通用的代码以方便于一般用户的操作，更兼得前几日又有人问到这个问题，于是，经过断断续续的细化修改，一段不算长但是自认为设计思想相当优秀的通用代码终于调试完毕了。 
 
警告：本文仅在SQL Server 2000环境下测试通过。
正　文:

   相信无论是熟悉还是不熟悉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 tepid=@intvalue
Select @chvusers=sysusers.name
FROM sysusers join sysobjects
ON sysusers.uid=sysobjects.uid
Where sysobjects.name=@chvtablename
--取得相应对象的所有者，因为不可能在所有的数据库中任何对象的所有者都是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),
 

Access软件网QQ交流群 (群号:54525238) 　　　　　　Access源码网店