Skip Navigation Links
Home
Functions
Tips & Tricks
SQL Server 2005
SQL Server 2008
SQL Server 2012
FAQ
Forums
Practice Test
Bookstore
Tip of the Day : How to Get a List of User Views Within a Database
SQL Server Helper
Home > > Tip of the Day
SQL Server Helper - Tip of the Day

There are different ways of listing user views within a database.  The first method is with the sp_tables system stored procedure:

EXECUTE sp_tables @Table_Type = '''VIEW''', 
@Table_Owner = 'dbo'

The sp_tables system stored procedure returns a list of objects that can be queried in the current environment.  The sp_tables system stored procedure returns user tables, views and system tables.  To return only view, the @Table_Type parameter needs to be passed a value of ‘VIEW’.  So as not to include system views, the @Table_Owner parameters needs to be passed a value of ‘dbo’ or whatever is the owner of the views.

Another way of getting a list of views is with the INFORMATION_SCHEMA.VIEWS system view.  The INFORMATION_SCHEMA.VIEWS system view returns one row for each view in the current database for which the current user has permission.

SELECT [TABLE_NAME]
FROM INFORMATION_SCHEMA.VIEWS

Alternatively, the INFORMATION_SCHEMA.TABLES system view can also be used to return user views.

SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'VIEW'

The INFORMATION_SCHEMA.TABLES system view returns both view and user tables.  To return just user views, the TABLE_TYPE needs to be checked for a value of ‘VIEW’.

The third way of getting a list of user views is by querying the different system views, namely, the sys.views, sys.objects and dbo.sysobjects.

SELECT [Name] FROM [sys].[views]
SELECT [Name] FROM [sys].[objects] WHERE [type] = 'V'
SELECT [Name] FROM [dbo].[sysobjects] WHERE [xtype] = 'V'

Back to Tip of the Day List Next Tip