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 date
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

--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.name
FROM syscomments sc
INNER JOIN sysobjects so ON sc.id=so.id
WHERE sc.TEXT LIKE ‘‘%P_ThisIsAStoredProcedure%‘‘‘

EXEC sp_MSforeachdb @sql

 

Run same command on all SQL Server databases without cursors

标签：