從SQLSERVER6.5開始,MS提供了一個非常有用的系統存儲過程sp_MSforeachtable和sp_MSforeachDB;作為DBA會經常需要檢查所有的數據庫或用戶表,比如:檢查所有數據庫的容量;看看指定數據庫所有用戶表的容量,所有表的記錄數...,我們一般處理這樣的問題都是用游標分別處理處理,比如:在數據庫檢索效率非常慢時,我們想檢查數據庫所有的用戶表,我們就必須這樣寫游標:
DECLARE @TableName varchar(255)
DECLARE @ExeSQL varchar(4000)
DECLARE Table_Cursor CURSOR FOR SELECT [name] FROM sysobjects WHERE xtype='U'
OPEN Table_Cursor
FETCH NEXT FROM Table_Cursor INTO @TableName
WHILE(@@FETCH_STATUS=0)
BEGIN
PRINT @TableName
SELECT @ExeSQL='DBCC CHECKTABLE('''+@TableName+''')'
EXEC(@EXESQL)
FETCH NEXT FROM Table_Cursor INTO @TableName
END
CLOSE Table_Cursor
DEALLOCATE Table_Cursor
GO
如果我們用sp_MSforeachtable就可以非常方便的達到相同的目的:
EXEC sp_MSforeachtable @command1="print '?' DBCC CHECKTABLE('?')"
大家可以看出這樣就更加簡潔(雖然在後台也是通過游標來處理的),下面我們就仔細分析一下sp_MSforeachtable這個存儲過程:
我們看看sp_MSforeachtable詳細的CODE:
USE MASTER
GO
SP_HELPTEXT sp_MSforeachtable
--下面時sp_MSforeachtable的原始代碼
CREATE proc sp_MSforeachtable
@command1 nvarchar(2000), @replacechar nchar(1) = N'?', @command2 nvarchar(2000) = null,
@command3 nvarchar(2000) = null, @whereand nvarchar(2000) = null,
@precommand nvarchar(2000) = null, @postcommand nvarchar(2000) = null
as
/* This proc returns one or more rows for each table (optionally, matching @where), with each table defaulting to its
own result set */
/* @precommand and @postcommand may be used to force a single result set via a temp table. */
/* Preprocessor won't replace within quotes so have to use str(). */
declare @mscat nvarchar(12)
select @mscat = ltrim(str(convert(int, 0x0002)))
if (@precommand is not null)
exec(@precommand)
/* Create the select */
exec(N'declare hCForEach cursor global for select ''['' + REPLACE(user_name(uid), N'']'', N'']]'') + '']'' + ''.'' + ''[''
+ REPLACE(object_name(id), N'']'', N'']]'') + '']'' from dbo.sysobjects o '
+ N' where OBJECTPROPERTY(o.id,
