patternsqlModerate

Differentiating tables and views in INFORMATION_SCHEMA.COLUMNS

Submitted by: @import:stackexchange-dba·Mar 10, 2026·

Viewed 0 times

tablescolumnsinformation_schemaviewsanddifferentiating

Problem

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.

Solution

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

Code Snippets

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

Context

StackExchange Database Administrators Q#7647, answer score: 10

Revisions (0)

No revisions yet.