lpfy lpfy - 1 month ago 5
SQL Question

How to list the source table name of columns in a VIEW (SQL Server 2005)

Just wondering how to list column name and table name in one query for a view.

For example:

A view named as

viewC
, create by
tbl1 inner join tbl2
, contain
a,b,c,d
columns (
a,b
from
tbl1
and
c,d
from
tbl2
).

How to

Select COLUMN_NAME, DATA_TYPE, column_default, character_maximum_length, sourceTableNAME
FROM information_schema.columns
where table_name='viewC'


together?

Answer

This information is available from the INFORMATION_SCHEMA views:

SELECT * 
FROM    INFORMATION_SCHEMA.VIEW_COLUMN_USAGE AS cu
JOIN    INFORMATION_SCHEMA.COLUMNS AS c
ON      c.TABLE_SCHEMA  = cu.TABLE_SCHEMA
AND     c.TABLE_CATALOG = cu.TABLE_CATALOG
AND     c.TABLE_NAME    = cu.TABLE_NAME
AND     c.COLUMN_NAME   = cu.COLUMN_NAME
WHERE   cu.VIEW_NAME    = '<your view name>'
AND     cu.VIEW_SCHEMA  = '<your view schema>'

If your view includes tables from more than one database, the query will become considerably more complex

Comments