How to find out the views created on a table in SQL Server 2005

In order to find out the views created on a table, you can use the Information Schema Views. This is one of the several methods that SQL Server 2005 provides for obtaining metadata.

According to the BOL, the Information_Schema.View_Table_Usage "Contains one row for each table, in the current database, used in a view" . Let us see how to use it to find out the views on a given table:

QUERY

USE NORTHWIND
SELECT VIEW_NAME FROM information_schema.view_table_usage
WHERE table_name = 'Categories'

returns you the view names on the table Categories.

Note: information schema view returns information about the objects to which the current user has permissions.