To iterate or execute command on All SQL Server databases

EXEC sp_msforeachdb ‘USE ?; IF ”?” NOT IN (”master”, ”tempdb”, ”model”, ”msdb”) CREATE ROLE db_executor;
IF ”?” NOT IN (”master”, ”tempdb”, ”model”, ”msdb”) GRANT EXECUTE TO db_executor;’
——****************
——OR
——****************

DECLARE @command varchar(max)
SELECT @command = ‘USE ?;
IF ”?” NOT IN (”master”, ”tempdb”, ”model”, ”msdb”)
select name
from sysobjects o, syscomments s
where o.id = s.id
and text like ”%”+ ”xxx” +”%” + ”yyyyy” +”%” + ”xxx” +”%”
and o.xtype = ”P”;

EXEC sp_MSforeachdb @command

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s