Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

People also ask

Does view exist in database?

Like a table, a view consists of a set of named columns and rows of data. Unless indexed, a view does not exist as a stored set of data values in a database. The rows and columns of data come from tables referenced in the query defining the view and are produced dynamically when the view is referenced.


FOR SQL SERVER

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

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

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.


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

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