对全部数据库运行同一个SQL命令.docVIP

对全部数据库运行同一个SQL命令 我发现有些时候需要在某个SQL Server实例里运行作用于所有数据库的SQL命令。虽然没有经过官方证实，但是有一个便易的存储过程，让你可以轻松做到这一步而无需在主数据库里的 sysdatabases表里设置指针，这个存储过程就是：sp_MSforeachdb。那么这个存储过程的句法应该是怎么样的?可否给出一些实例? 　　专家解答： 　　这个存储过程的句法是：　 以下是引用片段：　EXEC?sp_MSforeachdb?@command 　　(其中@command 是一个长度可变的字符串。) 　　例一：在一个SQL实例里查询所有数据库的信息： 　　--这个查询会在一个SQL实例里返回所有数据库的全部表的清单： 以下是引用片段：　　DECLARE?@command?varchar(1000) 　　SELECT?@command?=?USE???SELECT?name?FROM?sysobjects?WHERE?xtype?=?U?ORDER?BY?name 　　EXEC?sp_MSforeachdb?@command 　　你也可以省略掉声明和设置@command变量的过程。下面的T-SQL命令与上面的命令可以返回同样的结果，只不过这个命令简略成一行代码： 　　--这个查询会在一个SQL实例里返回所有数据库的全部表的清单： 以下是引用片段：　　EXEC?sp_MSforeachdb?USE???SELECT?name?FROM?sysobjects?WHERE?xtype?=?U?ORDER?BY?name 　　例二：在一个SQL实例里执行对所有用户数据库的DDL查询。 　　--这个语句会在每一个用户数据库里创建一个存储过程，该存储过程会返回一个数据库里所有用户的清单，以最后修改日期进行排序。 以下是引用片段：　　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_MSforeachdb存储过程的范围的时候需要考虑很多因素，尤其是在创建或者修改对象的时候。编码时需要通过 使用T-SQL关键词GEGIN和END来设置，IF语句为true的情况下存储过程也会执行。你需要注意，USE?语句是包含在GEGIN…END里面 的。同样要牢记关键的T-SQL规则及其说明。在现在这种情况下，当创建一个过程时，CREATE PROCEDURE短语必须是首先执行的第一行代码。你可以把代码CREATE PROCEDURE包含在EXEC()函数里面。 　　那么“?”占位符呢? 　　在以上的例子中，你可以看到经常使用问号作为占位符代替数据库/数据库名。要引用数据库名作为查询返回的字符串，可以把它置于两对单引号里面。 如果要把它作为数据库对象的索引，则单独使用它(如在例三所示)。有必要通过使用USE?语句设置查询所需的数据库，否则代码就会执行于当前数据库。如果 你在当前实例里有五个数据库，而你想要在DBx数据库里运行以上存储代码，它就会在DBx数据库里运行@command的T-SQL文本5次。如例三返回 结果所示。 例三：在一个SQL实例里查询所有数据库的文件信息 　　--该查询会返回一个SQL实例中所有数据库的全部文件清单： 以下是引用片段：　　EXEC?sp_MSforeachdb?USE???SELECT??,?SF.filename,?SF.size?FROM?sys.sysfiles?SF 　　如果我们省略掉限制查询范围的USE?从句，会出现什么结果呢?从下面你可以看到，虽然看起来代码对每一个数据库都执行了命令，但是内容都是一样的。注意filename列，你可以看到查询是在主数据库里对每一个数据库执行查询命令的。 　　--删掉USE?从句，最后的结果就是查询在当前数据库里重复执行：　 以下是引用片段：　EXEC?sp_MSforeachdb?SELECT??,?SF.filename,?SF.size?FROM?sys.sysfiles?SF 　　为什么不干脆使用Cursor呢?当然，使用一个cursor就可以完成我刚才使用存储过程得到的结果，但是让我们来看看要执行例三中使用的命令，需要编