October 4, 2007 at 1:52 pm
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.
October 4, 2007 at 2:08 pm
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?
October 4, 2007 at 4:00 pm
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.
October 5, 2007 at 5:19 am
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