以下内容结合网上资源 整理而成。。
MSforeachtable和sp_MSforeachdb 分别用于遍历某数据库的每个用户表、每个数据库。
sp_MSforeachtable
详细的CODE不在此贴出 你可以 通过
USE MASTER
GO
SP_HELPTEXT sp_MSforeachtable
得到它的原代码
这个系统存储过程有7个参数:
@command1 nvarchar(2000), --第一条运行的T-SQL指令
@replacechar nchar(1) = N'?', --指定的占位符号
@command2 nvarchar(2000) = null,--第二条运行的T-SQL指令
@command3 nvarchar(2000) = null, --第三条运行的T-SQL指令
@whereand nvarchar(2000) = null, --可选条件来选择表
@precommand nvarchar(2000) = null, --在表前执行的指令
@postcommand nvarchar(2000) = null --在表后执行的指令
现在我来介绍一些用法
--获得每个表的记录数和容量
1.EXEC sp_MSforeachtable @command1="print '?'",
@command2="sp_spaceused '?'",
@command3= "SELECT count(*) FROM ? "
2.exec sp_MSforeachtable 'select ''?''','?',
'sp_spaceused ''?''',
'SELECT count(*) FROM ? '
（这2种写法等价 第二种 没有参数 动态'?'写成 ''?''）
--获得所有的数据库的存储空间
exec sp_MSforeachdb 'select ''?''','?','sp_spaceused '
--删除当前数据库所有表中的数据
sp_MSforeachtable 'Delete from ?'
sp_MSforeachtable @command1="Delete from ? "
sp_MSforeachtable 'Truncate Table ?'
--更新Table1/Table2中note列为NULL的值
sp_MSforeachtable 'Update ? Set note='''' Where note is null',null,null,null,' AND o.name in (''Table1'',''Table2'')'
--更改对象所有者
exec sp_msforeachtable 'sp_changeobjectowner ''?'', ''CCUSER'''
EXEC sp_MSforeachtable @command1="EXEC SP_ChangeObjectOwner '?','CCUSER'"
sp_MSforeachDB除了@whereand外,和sp_MSforeachtable的参数是一样的,我们可以通过这个存储过程检测所有的数据库,比如:
1.检查所有的数据库
EXEC sp_MSforeachdb @command1="print '?'",
@command2="DBCC CHECKDB (?) "
其他一些sql中的扩展存储的总结:
xp_availablemedia （无） 显示系统上可用的盘符'C:/' xp_availablemedia
xp_enumgroups 列出当前系统的使用群组及其说明 xp_enumgroups
xp_enumdsn （无） 列出系统上已经设置好的ODBC数据源名称 xp_enumdsn
xp_dirtree （无） 显示某个目录下的子目录与文件架构 xp_dirtree 'C:/inetpub/wwwroot/'
xp_getfiledetails （无） 获取某文件的相关属性 xp_getfiledetails 'C:/inetpub/wwwroot.asp'
dbp.xp_makecab （无） 将目标计算机多个档案压缩到某个档案里所压缩的档案都可以接在参数的后面用豆号隔开 dbp.xp_makecab 'C:/lin.cab','evil',1,'C:/inetpub/mdb.asp'
xp_unpackcab （无） 解压缩 xp_unpackcab 'C:/hackway.cab','C:/temp',1
xp_ntsec_enumdomains （无） 列出服务器域名 xp_ntsec_enumdomains
xp_servicecontrol （无） 停止或者启动某个服务 xp_servicecontrol 'stop','schedule'
xp_terminate_process （无） 用pid来停止某个执行中的程序 xp_terminate_process 123
dbo.xp_subdirs （无） 只列某个目录下的子目录 dbo.xp_subdirs 'C:/'
接下来的是网上一个高手自己创建的类似存储过程 相当强大
USE MASTER
GO
CREATE proc sp_MSforeachObject
@objectType int=1,
@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)
/* Defined @isobject for save object type */
Declare @isobject varchar(256)
select @isobject= case @objectType
when 1 then 'IsUserTable'
when 2 then 'IsView'
when 3 then 'IsTrigger'
when 4 then 'IsProcedure'
when 5 then 'IsDefault'
when 6 then 'IsForeignKey'
when 7 then 'IsScalarFunction'
when 8 then 'IsInlineFunction'
when 9 then 'IsPrimaryKey'
when 10 then 'IsExtendedProc'
when 11 then 'IsReplProc'
when 12 then 'IsRule'
end
/* Create the select */
/* Use @isobject variable isstead of IsUserTable string */
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, N'''+@isobject+''') = 1 '+N' and o.category & ' + @mscat + N' = 0 '
+ @whereand)
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)
return @retval
GO
--1.获得所有的存储过程的脚本:
EXEc sp_MSforeachObject @command1="sp_helptext '?' ",@objectType=4
--2.获得所有的视图的脚本:
EXEc sp_MSforeachObject @command1="sp_helptext '?' ",@objectType=2
--3.比如在开发过程中,没一个用户都是自己的OBJECT OWNER,所以在真实的数据库时都要改为DBO:
EXEc sp_MSforeachObject @command1="sp_changeobjectowner '?', 'dbo'",@objectType=1
EXEc sp_MSforeachObject @command1="sp_changeobjectowner '?', 'dbo'",@objectType=2
EXEc sp_MSforeachObject @command1="sp_changeobjectowner '?', 'dbo'",@objectType=3
EXEc sp_MSforeachObject @command1="sp_changeobjectowner '?', 'dbo'",@objectType=4
再次声明 这个自定义存储过程是出自http://blog.csdn.net/flynetcn/archive/2007/05/30/1631762.aspx
暂时整理到这