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

Question

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

Solution 1

FOR SQL SERVER

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

Solution 2

There are already many ways specified above but one of my favourite is missing..

GO
IF OBJECT_ID('nView', 'V') IS NOT NULL
    DROP VIEW nView;
GO

WHERE nView is the name of view

UPDATE 2017-03-25: as @hanesjw suggested to drop a Store Procedure use P instead of V as the second argument of OBJECT_ID

GO
IF OBJECT_ID( 'nProcedure', 'P' ) IS NOT NULL 
    DROP PROCEDURE dbo.sprocName; 
GO

Solution 3

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.

Solution 4

if exists (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[MyTable]') )

Solution 5

For people checking the existence to drop View use this

From SQL Server 2016 CTP3 you can use new DIE statements instead of big IF wrappers

syntax

DROP VIEW [ IF EXISTS ] [ schema_name . ] view_name [ ...,n ] [ ; ]

Query :

DROP VIEW IF EXISTS view_name

More info here

View more solutions

Comments

[Admin]

Using sqlite: SQL error: no such table: INFORMATION_SCHEMA.VIEWS

[Eric]

You probably want to join in sys.schema here, as well.

[Alix Axel]

@lutz: +1, for lack of support on SQLite.

[Steam]

Error -Invalid object name 'sys.views'. I was querying master DB

[FrostbiteXIII]

If you found this to decide between a CREATE and ALTER for a view (as I did), this doesn't work for VIEWs - you have to DROP the VIEW* and then CREATE it. The IF EXISTS still works fine for DROPing the VIEW tho, so thanks! :) * Don't forget about any permissions when you do. ;)

[kemiller2002]

Try this. if it doesn't exist create the view (just a stub) and then alter that stub to put your updates in. That way you never have to drop it. structuredsight.com/2014/03/12/non-failing-scripts

[Jimmy Bosse]

For Microsoft SQL Server, I find this the most useful because IF EXISTS is often used when creating schema management scripts. In the script you probably already have the CREATE ViEW [dbo].[MyView] and the above is this simplest snippet for copy and paste.

[Phillip Senn]

I like this one. You can use 'u' for tables as well.

[hanesjw]

Or 'P' for stored procedures. IF OBJECT_ID ( 'dbo.sprocName', 'P' ) IS NOT NULL DROP PROCEDURE dbo.sprocName; GO

[Eric J.]

I don't know if this was the best answer in 2009, but it seem to be in 2016 (though SQL Server 2016 is introducing an even better option).

[aruno]

OBJECT_ID doc msdn.microsoft.com/en-us/library/ms190328.aspx - and this one gives you all the object types : msdn.microsoft.com/en-us/library/ms190324.aspx

[Reversed Engineer]

Presumably one should put the name of the view being checked in the quotes? Otherwise this will never work :)