155

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

0

10 Answers 10

189

FOR SQL SERVER

IF EXISTS(select * FROM sys.views where name = '')
5
  • 7
    You probably want to join in sys.schema here, as well.
    – Eric
    Commented Aug 20, 2009 at 13:07
  • Error -Invalid object name 'sys.views'. I was querying master DB
    – Steam
    Commented Nov 20, 2013 at 18:02
  • 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. ;) Commented May 21, 2014 at 10:40
  • 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 Commented May 21, 2014 at 13:45
  • Presumably one should put the name of the view being checked in the quotes? Otherwise this will never work :) Commented Aug 21, 2017 at 12:54
163

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
4
  • 8
    I like this one. You can use 'u' for tables as well. Commented Oct 10, 2015 at 22:47
  • 3
    Or 'P' for stored procedures. IF OBJECT_ID ( 'dbo.sprocName', 'P' ) IS NOT NULL DROP PROCEDURE dbo.sprocName; GO
    – hanesjw
    Commented Jan 29, 2016 at 18:45
  • 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).
    – Eric J.
    Commented Feb 16, 2016 at 18:39
  • 1
    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 Commented May 26, 2016 at 7:04
60

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.

2
  • 4
    Using sqlite: SQL error: no such table: INFORMATION_SCHEMA.VIEWS
    – lutz
    Commented Aug 20, 2009 at 12:59
  • @lutz: +1, for lack of support on SQLite.
    – Alix Axel
    Commented Aug 21, 2009 at 22:50
18
if exists (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[MyTable]') )
1
  • 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. Commented Dec 2, 2014 at 16:53
17

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

0
6

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
1

if it's Oracle you would use the "all_views" table.

It really depends on your dbms.

1

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

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
0

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));
        }
    }

Not the answer you're looking for? Browse other questions tagged or ask your own question.