5

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.

10

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
  • 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
  • Any reason for not using an INNER JOIN instead? – Daniel Liuzzi Feb 22 '17 at 15:01
  • The Op asked for which ones were views and which weren't. An inner join would have removed the ones for tables. – mrdenny Mar 3 '17 at 8:12
6

The query posted by @mrdenny didn't seem to return reliable data for me. I had more luck this way.

select b.TABLE_TYPE, a.*
from INFORMATION_SCHEMA.COLUMNS a
LEFT OUTER JOIN INFORMATION_SCHEMA.TABLES b ON a.TABLE_CATALOG = b.TABLE_CATALOG
    AND a.TABLE_SCHEMA = b.TABLE_SCHEMA
    AND a.TABLE_NAME = b.TABLE_NAME

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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