如何列出 VIEW 中列的源表名称 (SQL Server 2005) [英] 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.

例如:

一个名为viewC的视图，由tbl1内连接tbl2创建，包含a,b,c,d列(a,b 来自 tbl1 和 c,d 来自 tbl2).

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).

如何

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

一起?

推荐答案

此信息可从 INFORMATION_SCHEMA 视图获得:

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

这篇关于如何列出 VIEW 中列的源表名称 (SQL Server 2005)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！