query help

Question

query help

pshaship

SSCertifiable

Points: 6125
More actions
August 18, 2011 at 1:09 pm

#255951

I want to run the below same query for 50 databases
How could we do that in single step instead of doing per database?
use [WSS_Content]
if not exists
(select * from sys.symmetric_keys
where name = N'##MS_DatabaseMasterKey##')
create master key encryption by password = N'Admin Key Password !2#4'
thanx

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Kurt W. Zimmerman SSCrazy Eights Points: 8676 More actions · Answer 1

pshaship (8/18/2011)
I want to run the below same query for 50 databases
How could we do that in single step instead of doing per database?
use [WSS_Content]
if not exists
(select * from sys.symmetric_keys
where name = N'##MS_DatabaseMasterKey##')
create master key encryption by password = N'Admin Key Password !2#4'
thanx

I'm assuming the databases reside all on the same server?

Kurt

Kurt W. Zimmerman
SR DBA
Lefrak Organization
New York, NY

http://www.linkedin.com/in/kurtwzimmerman

Neal Sivley SSCommitted Points: 1766 More actions · Answer 2

Neal Sivley

SSCommitted

Points: 1766

August 18, 2011 at 2:04 pm

#1370383

look at stored proc sp_msforeachdb

Lowell SSC Guru Points: 323518 More actions · Answer 3

i think something like this would be better than sp_msForEachDB:

I prever to generate possible statements, review and prune,a nd then run them when I'm ready

/*

USE SandBox; create master key encryption by password = N'NotARealPassword'

USE Waffle; create master key encryption by password = N'NotARealPassword'

USE Waffle2; create master key encryption by password = N'NotARealPassword'

USE Sunrise; create master key encryption by password = N'NotARealPassword'

*/

SELECT

'USE ' + NAME + '; create master key encryption by password = N''NotARealPassword'' '

FROM sys.databases WHERE database_id > 4

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!