Getting VIEW metadata from SQL Server using T-SQL

Question

I'm trying to get description of the views on my database, and I tried joining INFORMATION_SCHEMA.VIEWS with sys.extended_properties.

Problem is that I can't find ID in INFORMATION_SCHEMA.VIEWS

Anyone know how to solve the problem?

Thanks in advance.

Gottfried Lesigang · Accepted Answer · 2017-01-03 22:12:58Z

2

You'll get the object's id like this:

SELECT * FROM sys.objects WHERE type='v'

You might join the extended properties like this

SELECT * FROM sys.objects AS o
INNER JOIN sys.extended_properties AS ep ON ep.major_id=o.object_id;

answered Jan 3, 2017 at 22:12

67.6k9 gold badges60 silver badges124 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Or better yet: use the focused sys.views catalog view instead of having to remember what obscure type a view has in sys.objects ....

@marc_s Well, yes, fair point. But - to be honest - a "V" to mark a VIEW is not very obscure :-)

Well yes - for the view it's pretty obvious - and therefore, you'd think it would be t for table and f for function - right?!?!?

@marc_s btw; You brought up the f for function There is no focused view for this... :-)