Run same command on all SQL Server databases without cursors

標籤：

original: https://www.mssqltips.com/sqlservertip/1414/run-same-command-on-all-sql-server-databases-without-cursors/

--This query will return a listing of all tables in all databases on a SQL instance: DECLARE @command varchar(1000) SELECT @command = ‘USE ? SELECT name FROM sysobjects WHERE xtype = ‘‘U‘‘ ORDER BY name‘ EXEC sp_MSforeachdb @command

--This statement creates a stored procedure in each user database that will return a listing of all users in a database, sorted by their modification dateDECLARE @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

--This query will return a listing of all files in all databases on a SQL instance:EXEC sp_MSforeachdb ‘USE ? SELECT ‘‘?‘‘, SF.filename, SF.size FROM sys.sysfiles SF‘

 

查詢跨庫預存程序調用，沒有直接的方法：

DECLARE @sql NVARCHAR(2000)=‘USE ? SELECT DISTINCT so.nameFROM syscomments scINNER JOIN sysobjects so ON sc.id=so.idWHERE sc.TEXT LIKE ‘‘%P_ThisIsAStoredProcedure%‘‘‘EXEC sp_MSforeachdb @sql

 

Run same command on all SQL Server databases without cursors