Question on subtracting two queries

  • I want to return the difference number of records.

    Here are the queries:

    --subtracting columns with columns and descriptions (columns - columns and descriptions)

    --difference of 30 records

    --243

    select

    h.name as 'DataBase Name'

    ,t.name as 'Table Name'

    ,c.name as 'Column Name'

    from sys.tables t

    inner join sys.schemas h

    on h.schema_id = t.schema_id

    inner join sys.columns c

    on t.object_id = c.object_id

    and not exists

    (

    --213

    select

    h.name as 'DataBase Name'

    ,t.name as 'Table Name'

    ,c.name as 'Column Name'

    from sys.extended_properties s

    inner join sys.tables t

    on s.major_id = t.object_id

    inner join sys.schemas h

    on h.schema_id = t.schema_id

    inner join sys.columns c

    on s.major_id = c.object_id

    and s.minor_id = c.column_id

    )

    I'm not getting anything back. I should be getting back 30 records that have null descriptions.

    Please help.

    Also,the way I was thinking was if I subtract the total number of columns that have descriptions from the total number of all columns, I would get back only the columns that do not have any descriptions. In otherwords, it would look something like this:

    column_name description

    column1 NULL

    Note: the alignment in the above example may be off. Bold goes with the letter right below that is not bold.

  • You're not doing a subtract at all (I understand that's the goal, but the syntax isn't doing that).

    You're getting no results because the NOT EXISTS syntax is wrong. Never mind the performance problem - if you want that to syntax to work - it would have to be correlated to the outer quiery in some way - but it's not.

    A SUBTRACT looks at set A, and tries to remove all objects that are in (or in this case, are related to) objects in set B. You've got an operation that says - "return all objects in A if set B has ANY members".

    How were you envisioning that items in the outer query would relate to the items in the inner query?

    Note that the EXISTS() syntax is probably NOT the right way to go if you plan on reusing this code in the future (because performance will SUCK). If it's a one-time deal - that might be another issue. But your immediate issue is that it's not correlated correctly.

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • From the output your query is producing try this simple change.

    select

    h.name as 'DataBase Name'

    ,t.name as 'Table Name'

    ,c.name as 'Column Name'

    from sys.tables t

    inner join sys.schemas h

    on h.schema_id = t.schema_id

    inner join sys.columns c

    on t.object_id = c.object_id

    left join sys.extended_properties s

    on s.major_id = c.object_id

    and s.minor_id = c.column_id

    where s.minor_id is null

    This can still be a poor performing query on complex databases, but it will list the columns your query was producing.

  • I understand what your saying. Thank you.

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

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