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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With