Tip of the Day : SQL Server Database Design - Twitter Tweets

SQL Server Helper - Tip of the Day

How to Get a List of User Views Within a Database

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.


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


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