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

User · Answer

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

User · Answer

If you want to check the validity and consistency of all the existing views you can use the following query  declare  viewName sysname declare  cmd sysname DECLARE check cursor CURSOR FOR  SELECT cast     SCHEMA NAME schema id        name     as sysname  AS viewname FROM sys views  OPEN check cursor FETCH NEXT FROM check cursor  INTO  viewName  WHILE   FETCH STATUS   0 BEGIN  set  cmd  select   from    viewName begin try exec   cmd  end try begin catch print  Error  The view    viewName   is corrupted    end catch FETCH NEXT FROM check cursor  INTO  viewName END  CLOSE check cursor  DEALLOCATE check cursor

User · Answer

You can check the availability of the view in various ways  FOR SQL SERVER  use sys objects  IF EXISTS     SELECT 1    FROM   sys objects    WHERE  OBJECT ID   OBJECT ID   schemaName   ViewName              AND Type Desc    VIEW    BEGIN     PRINT  View Exists  END   use sysobjects  IF NOT EXISTS      SELECT 1    FROM   sysobjects    WHERE  NAME     schemaName   ViewName             AND xtype    V    BEGIN     PRINT  View Exists  END   use sys views  IF EXISTS      SELECT 1    FROM sys views    WHERE OBJECT ID   OBJECT ID N  schemaName   ViewName      BEGIN     PRINT  View Exists  END   use INFORMATION SCHEMA VIEWS  IF EXISTS      SELECT 1    FROM   INFORMATION SCHEMA VIEWS    WHERE  table name    ViewName            AND table schema    schemaName    BEGIN     PRINT  View Exists  END   use OBJECT ID  IF EXISTS     SELECT OBJECT ID  ViewName    V     BEGIN     PRINT  View Exists  END   use sys sql modules  IF EXISTS      SELECT 1    FROM   sys sql modules    WHERE  OBJECT ID   OBJECT ID   schemaName   ViewName      BEGIN    PRINT  View Exists  END

User · Answer

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

User · Answer

IN SQL Server    declare  ViewName nvarchar 20   ViewNameExample   if exists SELECT 1 from sys objects where object Id object Id  ViewName  and Type Desc  VIEW   begin     -- Your SQL Code goes here      end

User · Answer

if it s Oracle you would use the  all views  table    It really depends on your dbms

User · Answer

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

User · Answer

To expand on Kevin s answer       private bool CustomViewExists string viewName                using  SalesPad Data DataConnection dc   yourconnection                        System Data SqlClient SqlCommand cmd   new System Data SqlClient SqlCommand String Format   IF EXISTS select   FROM sys views where name     0                    Select 1             else                 Select 0   viewName                cmd CommandType   CommandType Text              return Convert ToBoolean dc ExecuteScalar cmd

User · Answer

FOR SQL SERVER  IF EXISTS select   FROM sys views where name

User · Answer

if exists  SELECT   FROM sys views WHERE object id   OBJECT ID N  dbo   MyTable

[sql] How can I check if a View exists in a Database?

Examples related to sql

Examples related to sql-server

Examples related to database

Examples related to stored-procedures

Examples related to view