SQL SERVER - Store Procedure

John Miller
John Miller used Ask the Experts™
on
I have a SP in which i need to add one condition to check multiple databases by using sys.dm_database_encryption_keys table.

when encryption_state is 2 and percentage_completed is 0 in the results it should print the query i will provide.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
So? Where are you stuck?

Have you tried something?

Author

Commented:
I tried this

DECLARE @Test varchar(10)
SELECT @Test = Database_ID from
                             sys.dm_database_encryption_keys  
WHERE Encryption_state = 2 and complete_percentage = 0
If (@test not NULL)
Begin
PRINT("ALTER DB NAme")

If i have multiple DB's which meets the about. It should PRINT multiple ALTER statements with DB Names.
IT Engineer
Distinguished Expert 2017
Commented:
Mind that a variable can store only a single value so you'll never have this working for all databases.
Try this alternative solution:
SELECT 'ALTER DB ' + DB_NAME(Database_ID)
FROM sys.dm_database_encryption_keys  
WHERE Encryption_state = 2 and percent_complete = 0

Open in new window

Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Basically, I have no idea what you are up-to. The question doesn't shed much light on that. However, I can give you a technically correct answer.

You can do it as follows:

declare @databases nvarchar(max) = ''

SELECT @databases += N'ALTER DB ' + DB_NAME(Database_ID) + '
'
FROM sys.dm_database_encryption_keys  
WHERE Encryption_state = 2 and percent_complete = 0

select @databases

Open in new window

Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
John, a feedback will be appreciated.
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
Recommendation to close this question by accepting the above comments as solutions.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial