Problem using fn_listextendedproperty, Why does this query error on the fourth column on MSSQL2008

Question

If you're just going to vote this down and not tell me how to do better, what's the point? How can I learn to make the question better if you vote -1 and not leave me a comment.

select
   u.name,
   o.name,
   case (o.type) when 'S' then 'SYSTEM TABLE' else 'TABLE' end,
   (select convert(varchar(8000), value) from ::fn_listextendedproperty(NULL, 'user', u.name, 'table', o.name, null, null) where name = 'MS_Description') as coln
from
   sys.sysobjects o
   join sys.schemas  u on (u.schema_id = o.uid)
where
   o.type in ('U', 'S')
   and u.name = "something here"
order by 1, 2

Is there a simple fix to make it work or is it all F'ed up?

The error is:

SQLSTATE = 42000, Microsoft SQL Native Client, Incorrect Syntax near ‘u’

This help for the function is virtually useless.

This seems like a very anti-sql, anti-normalization of data, pretty obtuse way of storing table comments.

Why isn't there comments column on a all_tables view?

Update

hard coded values in the function work fine, passing the results of the query in, do not. I've never seen anything like this before.

** Update II **

I replaced the 2005 native driver with the 2007 native driver and it no longer errors out, but it returns ZERO tables. ODBC works fine.

Answer 1

Does it work if you use CROSS APPLY instead of a nested SELECT?

select
   u.name,
   o.name,
   case (o.type) when 'S' then 'SYSTEM TABLE' else 'TABLE' end,
   convert(varchar(8000), ex.value) as coln
from
   [%CATALOG%.]sys.sysobjects o
   join [%CATALOG%.]sys.schemas  u on (u.schema_id = o.uid)
   cross apply ::fn_listextendedproperty(NULL, 'user', u.name, 'table', o.name, null, null) ex
where
   o.type in ('U', 'S')
[  and u.name = %.q:OWNER%]
and ex.name = 'MS_Description'
order by 1, 2

Answer 2

You've got a bad open square bracket:

[  and u.name = %.q:OWNER%]

should be

and u.name = [%.q:OWNER%]

And I agree on those token things, I've never seen them before and I bet a lot of others haven't, either. I recommend replacing them.

I'd also fix that "order by 1,2" and use column names instead.