Link to home
Start Free TrialLog in
Avatar of motioneye
motioneyeFlag for Singapore

asked on

SQL queries to include Yes or No for the outputresults

Anyone can help me with below queries  when the result found empty, it should return with database name and status as Yes or No.

EXEC sp_MSforeachdb 'USE ?
SELECT db_name() AS Database_Name, name AS Key_Name
FROM sys.asymmetric_keys
WHERE key_length < 2048
AND db_id() > 4;'
ASKER CERTIFIED SOLUTION
Avatar of Pawan Kumar
Pawan Kumar
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Sample command for sp_MSforeachdb . Enjoy !!

--

declare @cmd varchar(5000)
set @cmd='SELECT r.name , CASE WHEN Key_Name IS NULL THEN ''No'' ELSE ''Yes'' END IsKey , Key_Name 
FROM
(
	SELECT db_name() AS Database_Name, name AS Key_Name
	FROM sys.asymmetric_keys
)t
FULL OUTER JOIN
(
	SELECT Name FROM sys.databases
)r
ON r.name = t.Database_Name
'
EXECUTE sp_msforeachdb @cmd
--

Open in new window

Avatar of Vitor Montalvão
when the result found empty
Please defina an empty result since database name can't be an empty string.