Differentiating tables and views in INFORMATION_SCHEMA.COLUMNS

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

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

Tags:

Sql Server