sybase 定期表分析脚本

CREATE PROCEDURE dbo.SP_INDEX_STATISTICS
AS
BEGIN
SET chained OFF
–变量定义
DECLARE @DB_NAME VARCHAR(30),@TABLE_NAME VARCHAR(100),@VS_SQL_TXT VARCHAR(4000)
SELECT @DB_NAME=DB_NAME()
DUMP TRANSACTION @DB_NAME WITH TRUNCATE_ONLY --清除日志—需保存SQL
DECLARE CUR CURSOR
FOR
–SELECT name from sysobjects WHERE type=‘U’
SELECT TB_NAME FROM sqmsanal.dbo.TB_UPDATE_INDEX_CRM
OPEN CUR
FETCH CUR INTO @TABLE_NAME
WHILE @@SQLSTATUS=0
BEGIN
SET @VS_SQL_TXT='DELETE STATISTICS '+@TABLE_NAME
EXEC (@VS_SQL_TXT)
COMMIT
SET @VS_SQL_TXT='UPDATE STATISTICS '+@TABLE_NAME
EXEC (@VS_SQL_TXT)
COMMIT
SET @VS_SQL_TXT='UPDATE INDEX STATISTICS '+@TABLE_NAME
EXEC (@VS_SQL_TXT)
COMMIT
FETCH CUR INTO @TABLE_NAME
END
CLOSE CUR
DEALLOCATE CUR
END