Query Extented Properties Of Tables/Views

Question

Post reply

Query Extented Properties Of Tables/Views

Alasdair Thomson

Hall of Fame

Points: 3630
More actions
October 14, 2008 at 8:31 am

#199764

Hi All,
We are trying to create a data dictionary for use with our reports etc. For all DB Tables and subsequest view have 7 extended properties ranging from source name, business definition etc.
The following code works well if I want all objects in a DB that has assigned Extended properties;
SELECT sys.schemas.name AS SchemaName ,sys.all_objects.name AS TableName, sys.all_columns.name AS ColumnName, sys.extended_properties.name AS PropertyName, sys.extended_properties.value AS PropertyValue
FROM sys.all_columns INNER JOIN
sys.all_objects ON sys.all_columns.object_id = sys.all_objects.object_id INNER JOIN
sys.schemas ON sys.all_objects.schema_id = sys.schemas.schema_id INNER JOIN
sys.extended_properties ON sys.all_columns.object_id = sys.extended_properties.major_id AND sys.all_columns.column_id = sys.extended_properties.minor_id
The thing is I want to be able to specify the table I am investigating, i.e have a where clause with the table name 'Table 2' in,
SELECT sys.schemas.name AS SchemaName ,sys.all_objects.name AS TableName, sys.all_columns.name AS ColumnName, sys.extended_properties.name AS PropertyName, sys.extended_properties.value AS PropertyValue
FROM sys.all_columns INNER JOIN
sys.all_objects ON sys.all_columns.object_id = sys.all_objects.object_id INNER JOIN
sys.schemas ON sys.all_objects.schema_id = sys.schemas.schema_id INNER JOIN
sys.extended_properties ON sys.all_columns.object_id = sys.extended_properties.major_id AND sys.all_columns.column_id = sys.extended_properties.minor_id
WHERE (sys.schemas.name = 'dbo') AND (sys.tables.name = 'Table 2')
But I get the following error: 'Msg 4104, Level 16, State 1, Line 1 The multi-part identifier "sys.tables.name" could not be bound.'
What am I doing wrong?

Viewing 6 posts - 1 through 5 (of 5 total)

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

Mark Cowne One Orange Chip Points: 26952 More actions · Answer 1

change

sys.tables.name = 'Table 2'

to

sys.all_objects.name = 'Table 2' and sys.all_objects.type='U'

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

Alasdair Thomson Hall of Fame Points: 3630 More actions · Answer 2

I tried the following as suggested, the code runs but I get results (properties info returned), I just get the column names defined and nothing else, even though the table does contain infomation in the extended properties...

Use udbEXTRACT

Go

SELECT sys.schemas.name AS SchemaName ,sys.all_objects.name AS TableName, sys.all_columns.name AS ColumnName, sys.extended_properties.name AS PropertyName, sys.extended_properties.value AS PropertyValue

FROM sys.all_columns INNER JOIN

sys.all_objects ON sys.all_columns.object_id = sys.all_objects.object_id INNER JOIN

sys.schemas ON sys.all_objects.schema_id = sys.schemas.schema_id INNER JOIN

sys.extended_properties ON sys.all_columns.object_id = sys.extended_properties.major_id AND sys.all_columns.column_id = sys.extended_properties.minor_id

WHERE (sys.schemas.name = 'dbo') AND (sys.all_objects.name = 'dbo.tblEXTRACT_RecoveriesClient') AND (sys.all_objects.type='U')

Mark Cowne One Orange Chip Points: 26952 More actions · Answer 3

I think

sys.all_objects.name = 'dbo.tblEXTRACT_RecoveriesClient'

should be

sys.all_objects.name = 'tblEXTRACT_RecoveriesClient'