|
Home > Archive > MS SQL Server > March 2006 > RE: Help Pls - Reporting Table Descriptions Using sys.extended_propert
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
RE: Help Pls - Reporting Table Descriptions Using sys.extended_propert
|
|
| John Bell 2006-03-05, 8:24 pm |
| Hi Simon
You may want to try:
SELECT t.name, e.value
FROM sys.tables AS t
LEFT JOIN sys.extended_properties AS e ON t.[object_id] = e.major_id AND
e.minor_id = 0 AND e.name = N'MS_Description'
ORDER BY t.name
This will list all tables that have a the 'MS_Description' extended property
for the table, if not the table is listed with a e.value as a NULL value.
John
"Simon" wrote:
> Hi, Using SQL Server 2005 I'm trying to write a view that joins two catalog
> views (sys.tables and sys. extended_properties)
to produce a two column
> result with the table name and it's description. Using the following T-SQL
> excludes tables that do not have a description (ie that have
> extended_properties but not the MS_Description field). Can anyone please
> assist. Thanks.
>
> SELECT TOP (100) PERCENT t.name, e.value
> FROM sys.tables AS t LEFT OUTER JOIN
> sys.extended_properties AS e ON t.object_id =
> e.major_id
> WHERE (e.name IS NULL OR
> e.name = N'MS_Description') AND (e.minor_id IS NULL OR
> e.minor_id = 0)
> ORDER BY t.name
>
>
>
| |
|
| John,
Many, many thanks for the solution. Was my mistake that I needed to put the
predicates in the FROM clause rather than the WHERE clause because the
predicates applied to the right side of the join (ie the extended_properties
view)?
The following quote from the definition of the FROM clause in BOL seems to
confirm this:
"There can be predicates that involve only one of the joined tables in the
ON clause. Such predicates also can be in the WHERE clause in the query.
Although the placement of such predicates does not make a difference for
INNER joins, they might cause a different result when OUTER joins are
involved. This is because the predicates in the ON clause are applied to the
table before the join, whereas the WHERE clause is semantically applied to
the result of the join"
Simon
"John Bell" < jbellnewsposts@hotma
il.com> wrote in message
news:FD5DF5CD-DFB7-4E4A-87FD- 90BBEEA30ACC@microso
ft.com...[color=darkred]
> Hi Simon
>
> You may want to try:
>
> SELECT t.name, e.value
> FROM sys.tables AS t
> LEFT JOIN sys.extended_properties AS e ON t.[object_id] = e.major_id AND
> e.minor_id = 0 AND e.name = N'MS_Description'
> ORDER BY t.name
>
> This will list all tables that have a the 'MS_Description' extended
> property
> for the table, if not the table is listed with a e.value as a NULL value.
>
> John
>
> "Simon" wrote:
>
|
|
|
|
|