DECLARE @command varchar(1000) SELECT @command = 'IF ''?'' NOT IN(''master'', ''model'', ''msdb'', ''tempdb'') BEGIN USE ? EXEC(''CREATE PROCEDURE pNewProcedure1 AS SELECT name, createdate, updatedate FROM sys.sysusers ORDER BY updatedate DESC'') END' EXEC sp_MSforeachdb @command 仔细研究一下,用你的代码替换上面的代码
我用sp_helptext查看过sp_MSforeachdb写出了自己的代码,你看看对你有没有用,有的话请给我点分,我是穷光蛋,嘿嘿。===================== use master go declare @a char(20) declare mycursor cursor for select name from sys.databases //此处可以添加where 做过滤条件 open mycursor fetch next from mycursor into @a while(@@fetch_status=0) begin exec(N'use ' + N'[' + @a + N']'+N' print db_name() ')//你可以把"print db_name)" 换成自己想执行的代码 fetch next from mycursor into @a end close mycursor deallocate mycursor =========================
--在所有库中都创建lo表
EXEC sp_MSforeachdb ' use [?] create table lo(id int)'
还有我用EXEC sp_MSforeachdb ' use [?] drop table lo' 之后,发现Master数据库中还有lo表,为什么呢
use master
go
declare @a char(20)
declare mycursor cursor for select name from sys.databases //此处可以添加where 做过滤条件
open mycursor
fetch next from mycursor into @a
while(@@fetch_status=0)
begin
exec(N'use ' + N'[' + @a + N']'+N' print db_name() ')//你可以把"print db_name)" 换成自己想执行的代码
fetch next from mycursor into @a
end
close mycursor
deallocate mycursor
=========================