Syntax

Question

Syntax

SQLAssAS

SSCertifiable

Points: 7246
More actions
October 23, 2014 at 4:46 am

#397549

SELECT
d.name as [Database Name]
from sys.databases d
Where name not in ('Msdb', 'Master', 'model', 'tempdb', 'DBA')
except
select REPLACE(name, ' - Backup Job', '')
from msdb.dbo.sysjobs
order by [Database Name]
The above code works fine, but i am running across my SQL Estate and storing the results.
As i don't just want a list of databases, I need to know which instances they are on. But I Can't just use
SELECT (select SERVERPROPERTY ('ServerName') ) as [ServerInstance],
d.name
from sys.databases d
Where name not in ('Msdb', 'Master', 'model', 'tempdb', 'DBA')
except
select REPLACE(name, ' - Backup Job', '')
from msdb.dbo.sysjobs
order by name
because of the EXCEPT (ll queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.)
How do i get around this?

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

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

HanShi SSC-Dedicated Points: 33506 More actions · Answer 1

First of all: there's no need to put the SERVERPROPERTY in a subselect. You can place it in the outer SELECT.

Second: the EXCEPT is similar to the NOT IN statement when using it on a single column. If you use the NOT IN you are not bound to the "equal number of columns".

Try this:

SELECT

SERVERPROPERTY ('ServerName') as [ServerInstance],

d.name as [Database Name]

from sys.databases d

Where name not in ('Msdb', 'Master', 'model', 'tempdb', 'DBA')

and name not in (

select REPLACE(name, ' - Backup Job', '')

from msdb.dbo.sysjobs

)

order by [Database Name]

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

SQLAssAS SSCertifiable Points: 7246 More actions · Answer 2

Thanks for that, i will read up on the differences between EXcept and not in

Ed Wagner SSC Guru Points: 287024 More actions · Answer 3

You could also use the built-in function @@servername to return the same thing.

SELECT @@SERVERNAME;

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

JHop34 SSC-Addicted Points: 448 More actions · Answer 4

Ed Wagner (10/23/2014)
You could also use the built-in function @@servername to return the same thing.
SELECT @@SERVERNAME;

This only works if the server is configured correctly. If the hostname has changed since installation this could be incorrect if it was'nt reconfigured.