Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a view which selects information from INFORMATION_SCHEMA.COLUMNS, which seems to give me both columns and views. Is there an easy way to tell which rows are columns and which are views? Should I do a comparison with INFORMATION_SCHEMA.VIEWS? I need both columns and views, I just want to be able to tell which is which.

share|improve this question
    
"both columns and views" doesn't seem to make sense. Do you mean it gives you columns both from base tables and from views? –  Martin Smith Nov 8 '11 at 9:21
add comment

1 Answer

up vote 2 down vote accepted

You will need to do a left outer join to INFORMATION_SCHEMA.VIEWS to see if they are views or not.

select CASE WHEN b.TABLE_NAME is not null then 'view' else 'table' end OBJECT_TYPE,
    a.*
from INFORMATION_SCHEMA.COLUMNS a
LEFT OUTER JOIN INFORMATION_SCHEMA.VIEWS b ON a.TABLE_CATALOG = b.TABLE_CATALOG
    AND a.TABLE_SCHEMA = b.TABLE_SCHEMA
    AND a.TABLE_NAME = b.TABLE_NAME
share|improve this answer
    
Can you expand on that? I have the join, but I want a column to indicate whether it's a view or not and I'm having trouble getting the syntax right. –  William Nov 7 '11 at 21:03
    
I've added some sample code. –  mrdenny Nov 7 '11 at 22:41
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.