How can I check if a View exists in a Database?

Question

Hi,

I have some SQL code that needs to be executed if a certain View exists in a database. How would I go about checking if the View exists?

EDIT: The DBMS being used is Microsoft SQL Server

Answer 1

FOR SQL SERVER

IF EXISTS(select * FROM sys.views where name = '')

Answer 2

This is the most portable, least intrusive way:

select
    count(*)
from
    INFORMATION_SCHEMA.VIEWS
where
    table_name = 'MyView'
    and table_schema = 'MySchema'

Edit: This does work on SQL Server, and it doesn't require you joining to sys.schemas to get the schema of the view. This is less important if everything is dbo, but if you're making good use of schemas, then you should keep that in mind.

Each RDBMS has their own little way of checking metadata like this, but information_schema is actually ANSI, and I think Oracle and apparently SQLite are the only ones that don't support it in some fashion.

Answer 3

if it's Oracle you would use the "all_views" table.

It really depends on your dbms.

Answer 4

What information do you obtain from knowing that there exists some view that has a particular name ?

If you are assuming that the mere existence of a view of the given name also implies that it is exactly the view that you expect it to be, it seems to me that's just asking for trouble.