For Each Database

Today I was writing a script that would go through all the databases on a server and add a user as a member of readonly and denywriter. It’s a script that I’ll probably need again, and so I discovered the “EXEC sp_MSForEachDB” stored procedure. This undocumented and clearly powerful sproc needs to be respected, however it’s certainly useful for batching a lot of commands quickly.

 EXECUTE master.sys.sp_MSforeachdb 'SELECT ''?'' '

You can also specify particular databases, so you can ignore system databases, or specify particular databases and execute a command against a subset of databases

EXEC sp_MSForEachDB 'IF ''?'' IN (''Contoso'',''AdventureWorks2012'')
select ''?'' '

2014-03-21 15_53_01

Author: Richie Lee

Full time computer guy, part time runner. Full time Dad, part time blogger. Knows a thing or two about Pokémon. Knows too much about SQL Agent. Writer of fractured sentences. Maker of the best damn macaroni cheese you've ever tasted.

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