October 19, 2020 at 6:35 am
select
case
when name = 'ValueA then 'Yes'
else 'No'
END
as MyDB from sys.sysdatabases where name like '%ValueA%') as DBPresent
It does not print No if ValueA is not presentas one of the values under name column.I get either no value or NULL.
I need Yes or No to be printed.
Thanks
October 19, 2020 at 7:26 am
Can you post the actual query please?
_____________
Code for TallyGenerator
October 19, 2020 at 8:11 am
Looks like you are missing a closing quote in the WHEN part of your case statement.
October 19, 2020 at 8:13 am
SELECT
name AS DBName,
CASE name
WHEN 'CustomerDB' THEN 'Yes'
ELSE 'No'
END as DBs
FROM sys.sysdatabases
where name='CustomerDb'
When CustomerDB is present it prints correct as below.
Columns: DBName Yes/No
CustomerDB Yes
I need the where clause to filter for only that row as I do not want all the other rows.
When the CustomerDB row itself is not present I obviously get an empty resultset but I need the below to be printed.
CustomerDB No
And I do not need all databases in sysbastabases table with Yes/No listed.
October 19, 2020 at 8:43 am
Since your query is returning no results, it cannot display a yes/no answer.
What you need to do is change it so that it always returns a result
SELECT CASE
WHEN EXISTS ( SELECT 1 FROM sys.sysdatabases WHERE name = 'CustomerDb' ) THEN 'Yes'
ELSE 'No'
END;
October 19, 2020 at 9:36 am
In this below query only if CustomerDB is present then the sql statements for columns JobAPresentAbsent, JobBPresentAbsent should execute if not under these columns JobAPresentAbsent, JobBPresentAbsent I should print CustomerDB is absent.How can this be achieved.
select (select @@servername) as ServerName,(select @@version) as Version,
(SELECT CASE
WHEN EXISTS ( SELECT name FROM sys.sysdatabases WHERE name = 'CustomerDB' )
THEN 'Present'
ELSE 'Absent'
END ) as 'SDMF',
(SELECT CASE
WHEN EXISTS ( SELECT name FROM msdb.dbo.sysjobs WHERE name = 'JobA' )
THEN 'Enabled'
ELSE 'Disabled'
END ) as 'JobAPresentAbsent',
(SELECT CASE
WHEN EXISTS ( SELECT name FROM msdb.dbo.sysjobs WHERE name = 'JobB' )
THEN 'Enabled'
ELSE 'Disabled'
END ) as 'JobBPresentAbsent'
October 19, 2020 at 12:10 pm
SELECT CASE
WHEN EXISTS ( SELECT name,enabled FROM msdb.dbo.sysjobs
WHERE name = 'MyJob )
THEN 'Exists'
ELSE 'Does not Exists'
END as 'SQL Job'
How do I add an additional column Job Status to the output ?
If Job exists and if enabled - print enabled; if job exists and disabled then print disabled.
If job does not exists then - print job does not exists
October 19, 2020 at 12:43 pm
SELECT CASE
WHEN EXISTS ( SELECT name,enabled FROM msdb.dbo.sysjobs
WHERE name = 'Myjob' )
THEN 'Exists'
ELSE 'Does not exist'
END as 'SQL Job'
How do I add the enabled column to the output here?
If job exists then print 'Exists' and its status as enabled or disabled as in the sysjobs table
if job does not exist then print 'Does not exist' and under the status column as 'No job no status'
October 19, 2020 at 8:37 pm
First, we're good boys and girls here, so we don't hardcode data. All the database/job names we put in some kind of table - temporary or permanent.
then we use outer join to system views to find out whatever we want about objects with those names:
senect n.ObjectName,
case when d.name is null then 'No' else 'Yes' end as [Is a Database],
case when j.name is null then 'No' else 'Yes' end as [Is a Job],
ISNULL (j.enabled, '') [is enabled]
FROM dbo.NamesToCheck n
LEFT JOIN master.sys.sysdatabases d on d.name=n.ObjectName
LEFT JOIN msdb.dbo.sysjobs j on j.name =n.ObjectName
you can add more joins to this query, if you need to check names of other types of objects
_____________
Code for TallyGenerator
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply