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. Pokémon Nut. 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: Logo

You are commenting using your 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