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

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