Transact SQL and the Non Exists Operator

Background

There is always something new to learn in SQL Land.

I tried to make a quick change this morning, and ended up with an error message.

Error Message

Here is the error message


Msg 156, Level 15, State 1, Line 33
Incorrect syntax near the keyword 'or'.
Msg 102, Level 15, State 1, Line 48
Incorrect syntax near ')'.

Scenario

Data Model

datamodel

Create Table


use [DBLAB]
go

if schema_id('notexists') is null
begin
    exec('create schema [notexists]  authorization [dbo]')
end
go

if object_id('[notexists].[author]') is not null
begin
    drop table [notexists].[author]
end
go

create table [notexists].[author]
(
      [authorID] bigint not null identity(1,1)
    , [authorName] varchar(120) not null

    , [addedBy] varchar(60) not null
        constraint constraintDefaultAuthorAddedBy default SYSTEM_USER

)
go

if object_id('[notexists].[publication]') is not null
begin
    drop table [notexists].[publication]
end
go

create table [notexists].[publication]
(
      [publicationID] bigint not null identity(1,1)
    , [publicationName] varchar(120) not null

    , [dateAdded] datetime not null
        constraint constraintDefaultPublicationDateAdded default getutcdate()

)
go

Query

List Tables

Our audit columns are addedBy and dateAdded.

List Audit Columns


declare @schema varchar(60)

set @schema = 'notexists'

select
          object_schema_name(tblSC.object_id) as [schema]
        , object_name(tblSC.object_id) as [table]
        , tblSC.name as [column]
        , tblST.name as [columnType]

from   sys.objects tblSO

            inner join sys.columns tblSC

                on tblSO.object_id = tblSC.object_id

            inner join sys.types tblST

                on tblSC.system_type_id = tblST.system_type_id

where  tblSO.type = 'U'

and    object_schema_name(tblSO.object_id) = @schema

and    tblSC.name in ( 'addedBy','dateAdded' )

order by
          object_schema_name(tblSC.object_id) --as [schema]
        , object_name(tblSC.object_id) --as [table]
        , tblSC.name --as [column]

Output:

ListTablesAndColumns

List Tables Missing One or More Audit Columns (Using Or Operator)

Let us list tables that do not have at least one of audit columns.


declare @schema varchar(60)

set @schema = 'notexists'

select
          OBJECT_SCHEMA_NAME(tblSO.object_id) as schemaName
        , tblSO.name as objectName

from   sys.objects tblSO

where  tblSO.type = 'U'

and  object_schema_name(tblSO.object_id) = @schema

and    not exists
            (

                -- Table has addedBy varchar column
                (

                    select tblSC.name, tblST.name
                    from   sys.columns tblSC
                            inner join sys.types tblST
                                on tblSC.system_type_id = tblST.system_type_id
                    where  tblSO.object_id = tblSC.object_id
                    and  tblSC.name = 'addedBy'
                    and  tblST.name in ('varchar', 'nvarchar')
                )

                or

                -- Table has dateAdded datetime column
                (

                    select tblSC.name, tblST.name
                    from   sys.columns tblSC
                            inner join sys.types tblST
                                on tblSC.system_type_id = tblST.system_type_id
                    where  tblSO.object_id = tblSC.object_id
                    and  tblSC.name = 'dateAdded'
                    and  tblST.name in ('date', 'datetime', 'smalldatetime')
                )

            )

go

 Output:

OrOperatorError

List Tables Missing One or More Audit Columns (Using Union Operator)

Let us see whether we can use the Union Operator.


declare @schema varchar(60)

set @schema = 'notexists'

select
          OBJECT_SCHEMA_NAME(tblSO.object_id) as schemaName
        , tblSO.name as objectName

from   sys.objects tblSO

where  tblSO.type = 'U'

and  object_schema_name(tblSO.object_id) = @schema

and    not exists
            (

                -- Table has addedBy varchar column
                select tblSC.name, tblST.name
                from   sys.columns tblSC
                        inner join sys.types tblST
                            on tblSC.system_type_id = tblST.system_type_id
                where  tblSO.object_id = tblSC.object_id
                and  tblSC.name = 'addedBy'
                and  tblST.name in ('varchar', 'nvarchar')

                union

                select tblSC.name, tblST.name
                from   sys.columns tblSC
                            inner join sys.types tblST
                                on tblSC.system_type_id = tblST.system_type_id
                where  tblSO.object_id = tblSC.object_id
                and  tblSC.name = 'dateAdded'
                and  tblST.name in ('date', 'datetime', 'smalldatetime')

            )

go

 Output:
UnionOperatorError

Explanation:

Nothing shows up.

List Tables Missing One or More Audit Columns (Using Multiple Not Exists Operator)

Let us see whether we can use Multiple Not Exists Operator.


declare @schema varchar(60)

set @schema = 'notexists'

select
          OBJECT_SCHEMA_NAME(tblSO.object_id) as schemaName
        , tblSO.name as objectName

from   sys.objects tblSO

where  tblSO.type = 'U'

and  object_schema_name(tblSO.object_id) = @schema

and
        (

            not exists
                (

                    -- Table has addedBy varchar column
                    (

                        select tblSC.name, tblST.name
                        from   sys.columns tblSC
                                inner join sys.types tblST
                                    on tblSC.system_type_id = tblST.system_type_id
                        where  tblSO.object_id = tblSC.object_id
                        and  tblSC.name = 'addedBy'
                        and  tblST.name in ('varchar', 'nvarchar')
                    )

                )

            or not exists

                (
                    select tblSC.name, tblST.name
                    from   sys.columns tblSC
                            inner join sys.types tblST
                                on tblSC.system_type_id = tblST.system_type_id
                    where  tblSO.object_id = tblSC.object_id
                    and  tblSC.name = 'dateAdded'
                    and  tblST.name in ('date', 'datetime', 'smalldatetime')
                )

            )

go

Explanation:

We have the result we expected.

MultipleNotExistsOperator

Summary

Thank goodness, the SQL Parser is far smarter than most of us.

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s