/*********************************************************************************
sp_MSforeachdb 系统存储过程有6个参数:
@command1 nvarchar(2000) --第一条运行的t-sql指令
@replacechar nchar(1) = N'?' --指定的占位符号
@command2 nvarchar(2000) = null --第二条运行的t-sql指令
@command3 nvarchar(2000) = null --第三条运行的t-sql指令
@precommand nvarchar(2000) = null --在sp_MSforeach_worker前执行的指令
@postcommand nvarchar(2000) = null --在sp_MSforeach_worker后执行的指令
************************************************************************************/
/*
* The following table definition will be created by SQLDMO at start of each connection.
* We don't create it here temporarily because we need it in Exec() or upgrade won't work.
*/
create proc sp_MSforeachdb
@command1 nvarchar(2000), @replacechar nchar(1) = N'?', @command2 nvarchar(2000) = null, @command3 nvarchar(2000) = null,
@precommand nvarchar(2000) = null, @postcommand nvarchar(2000) = null
as
set deadlock_priority low
/* This proc returns one or more rows for each accessible db, with each db 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 @inaccessible nvarchar(12), @invalidlogin nvarchar(12), @dbinaccessible nvarchar(12)
select @inaccessible = ltrim(str(convert(int, 0x03e0), 11))
select @invalidlogin = ltrim(str(convert(int, 0x40000000), 11))
select @dbinaccessible = N'0x80000000' /* SQLDMODbUserProf_InaccessibleDb; the negative number doesn't work in convert() */
if (@precommand is not null)
exec(@precommand)
declare @origdb nvarchar(128)
select @origdb = db_name()
/* If it's a single user db and there's an entry for it in sysprocesses who isn't us, we can't use it. */
/* Create the select */
exec(N'declare hCForEach cursor global for select name from master.dbo.sysdatabases d ' +
N' where (d.status & ' + @inaccessible + N' = 0)' +
N' and ((DATABASEPROPERTY(d.name, ''issingleuser'') = 0 and (has_dbaccess(d.name) = 1)) or ' +
N' ( DATABASEPROPERTY(d.name, ''issingleuser'') = 1 and not exists ' +
N' (select * from master.dbo.sysprocesses p where dbid = d.dbid and p.spid <> @@spid)))' )
declare @retval int
select @retval = @@error
if (@retval = 0)
exec @retval = sp_MSforeach_worker @command1, @replacechar, @command2, @command3
if (@retval = 0 and @postcommand is not null)
exec(@postcommand)
declare @tempdb nvarchar(258)
Select @tempdb = REPLACE(@origdb, N']', N']]')
exec (N'use ' + N'[' + @tempdb + N']')
return @retval
usage:
sp_msforeachdb除了@whereand外,和sp_msforeachtable的参数是一样的,我们可以通过这个存储过程检测所有的数据库,比如:
1.获得所有的数据库的存储空间:
exec sp_msforeachdb @command1="print '?'",
@command2="sp_spaceused "
2.检查所有的数据库
exec sp_msforeachdb @command1="print '?'",
@command2="dbcc checkdb (?) "
发表于 @ 2008年06月06日 10:12:16|评论(loading...)|编辑|收藏