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

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

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 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

FOR SQL SERVER  IF EXISTS select   FROM sys views where name

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 exists  SELECT   FROM sys views WHERE object id   OBJECT ID N  dbo   MyTable

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

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

[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