View and Underlying Table Information

 

 

When we are working with a view in a database, we must know that which table is used to create the view. In this article I am trying to illustrate these points.

To find, all the view name of your current database use.

SELECT name As View_Name

FROM   sys.sysobjects WHERE xtype='V'

 

GO

 

It shows you the entire view name in your current database.

Fine, now you want to know which underlying table is used in a particular view.

For example, suppose you have a view name "vw_salesInfo" and the view used two tables named "mast_customer", "tran_salesdtl". To see this information many developer open the view definition and search the T-SQL statements to find the underlying Table objects.

This is not a good procedure to find the underlying table objects information from view. To find that use this SQL statements.

SELECT DISTINCT VIEW_NAME,

                TABLE_NAME

FROM   INFORMATION_SCHEMA.VIEW_COLUMN_USAGE

WHERE  VIEW_NAME='vw_salesInfo'

 

GO

It gives you the view name and underlying table name related to it.

 

Hope this article is quite informative and thanking you to provide your valuable time on it.

 

Posted by: MR. JOYDEEP DAS