用sqlserver存储过程重建索引
我用sqlserver2005，如何用存储过程重建user表的索引，并且有一个返回值来判断是否重建索引成功
[最优解释]
如果语句是你想要的，就把它放到存储过程里面
Use [数据库名称]
Go
DECLARE @DBCCString NVARCHAR(1000)
DECLARE @TableName VARCHAR(100)
DECLARE Cur_Index CURSOR
FOR
SELECT Name AS TblName
FROM sysobjects
WHERE xType='U'
ORDER BY TblName
FOR READ ONLY
OPEN Cur_Index
FETCH NEXT FROM Cur_Index
INTO @TableName
WHILE @@FETCH_STATUS=0
BEGIN
SET @DBCCString = 'DBCC DBREINDEX(@TblName,'''')WITH NO_INFOMSGS'
EXEC SP_EXECUTESQL @DBCCString,N'@TblName VARCHAR(100)', @TableName
PRINT '重建表' + @TableName +'的索引........OK!'
FETCH NEXT FROM Cur_Index INTO @TableName
END
CLOSE Cur_Index
DEALLOCATE Cur_Index
PRINT '操作完成！'
go
create procedure P_rebuild_index
@i bit output
as
begin
alter index all on user rebuild
if @@error = 0
set @i = 1
else
set @i = 0
return @i
end
IF OBJECT_ID('pp') IS NOT NULL DROP PROC pp
GO
CREATE PROC pp
@name VARCHAR(128)--table name
AS
BEGIN
BEGIN TRY
EXEC('ALTER INDEX ALL ON '+@name+' REBUILD')
RETURN 1
END TRY
BEGIN CATCH
RETURN 0
END CATCH
END
--调用
DECLARE @i INT
EXEC @i=pp 'tb'
SELECT @i
IF EXISTS(SELECT 1 FROM SYS.OBJECTS WHERE NAME=N'P_REBUILDINDEX' AND TYPE='P')
DROP PROCEDURE P_REBUILDINDEX
GO
CREATE PROC P_REBUILDINDEX(@TBNAME VARCHAR(100))
AS
BEGIN
ALTER INDEX ALL ON '+@TBNMAE+' REBUILD
IF @@ERROR=O
PRINT 'SUCCESSFUL'
ELSE
PRINT 'FAILED'
END
EXEC P_REBUILDINDEX('user')
