Many a times we come across a scenario where we need to execute some code based on whether a View exists or not. There are different ways of identifying the View existence in Sql Server, this article will list out the commonly used approaches. Let me know which approach you use and reason for the same.

[ALSO READ] Views in Sql Server

To demonstrate these different approaches let us create a sample database SqlHintsDemoDB. Create Customers Table with sample data and a vwGetCustomerInfo View by the following script:

CREATE DATABASE SqlHintsDemoDB
GO
USE SqlHintsDemoDB
GO
--Create Customer table
CREATE TABLE dbo.Customers
( CustomerID int Identity(1,1), FirstName NVarchar(50),
  LastName NVarChar(50), Phone varchar(50))
GO
--Insert sample records into the customer table
INSERT INTO Customers (FirstName, LastName, Phone)
Values ('Kalpana','Biradar','2727272727'),
       ('Basavaraj','Biradar','1616161616')
GO
--Create view
CREATE VIEW dbo.vwGetCustomerInfo
AS
 SELECT CustomerID, FirstName +' ' + LastName FullName
 FROM CUSTOMERS
GO
SELECT * FROM dbo.vwGetCustomerInfo
GO

[ALSO READ] How to check if a Table exists in Sql Server

Approach 1: Using sys.views catalog view

We can write a query like below to check if a view vwGetCustomerInfo exists in the current database in any schema.

IF EXISTS(SELECT 1 FROM sys.views 
     WHERE Name = 'vwGetCustomerInfo')
	BEGIN
		PRINT 'View Exists'
	END

RESULT:

The above query checks the existence of the vwGetCustomerInfo View across all the schemas in the current database. Instead of this if you want to check the existence of the View in a specified Schema then we can re-write the above query as below:

IF EXISTS(SELECT 1 FROM sys.views 
   WHERE object_id = OBJECT_ID('dbo.vwGetCustomerInfo'))
	BEGIN
		PRINT 'View Exists'
	END

RESULT:

If you want to check the existence of a View in a database other than the current contextual database, then we can re-write the above query as shown below:

IF EXISTS(SELECT 1 FROM SqlHintsDemoDB.sys.views 
   WHERE object_id = OBJECT_ID('dbo.vwGetCustomerInfo'))
	BEGIN
		PRINT 'View Exists'
	END
GO

RESULT:

[ALSO READ] How to check if a record exists in table

Approach 2: Using sys.objects catalog view

sys.views catalog view inherits the rows from the sys.objects catalog view, sys.objects catalog view is referred to as the base view whereas sys.views is referred to as derived view. sys.views will return the rows only for the views, whereas sys.objects view apart from returning the rows for Views, it returns rows for the objects like: tables, stored procedure etc.

We can write a script like below to check the existence of a view in the current contextual database:

IF EXISTS (SELECT * FROM sys.objects
            WHERE object_id = OBJECT_ID('dbo.vwGetCustomerInfo')
                    AND type = 'V') 
	BEGIN
		PRINT 'View Exists'
	END

RESULT:

Approach 3: Using sys.sql_modules Catalog View

We can use the sys.sql_modules catalog view to check the existence of the View as shown below:

USE SqlHintsDemoDB
GO
IF EXISTS (SELECT 1 FROM sys.sql_modules
   WHERE object_id =  OBJECT_ID('dbo.vwGetCustomerInfo')
   AND OBJECTPROPERTY(object_id, 'IsView') = 1) 
	BEGIN
		PRINT 'View Exists'
	END

RESULT

Approach 4: Using OBJECT_ID() function

We can use OBJECT_ID() function like below to check if the View vwGetCustomerInfo exists in the current database.

IF OBJECT_ID(N'dbo.vwGetCustomerInfo', N'V') IS NOT NULL
	BEGIN
		PRINT 'View Exists'
	END

RESULT:

Specifying the Database Name and Schema Name parts for the View Name is optional. But specifying Database Name and Schema Name provides an option to check the existence of the View in the specified database and within a specified schema, instead of checking in the current database across all the schemas. The below query shows that, even though the current database is MASTER database, we can check the existence of the view vwGetCustomerInfo in the dbo schema in the SqlHintsDemoDB database.

USE master	
GO
IF OBJECT_ID(N'SqlHintsDemoDB.dbo.vwGetCustomerInfo', 
                  N'V') IS NOT NULL
	BEGIN
		PRINT 'View Exists'
	END

RESULT:

Approach 5: Avoid Using sys.sysobjects System table

We should avoid using sys.sysobjects System Table directly, direct access to it will be deprecated in some future versions of the Sql Server. As per Microsoft BOL link, Microsoft is suggesting to use the catalog views sys.objects/sys.views/sys.sql_modules instead of sys.sysobjects system table directly to check the existence of the View.

USE SqlHintsDemoDB
GO
IF EXISTS(SELECT 1 FROM sys.sysobjects  
     WHERE id = OBJECT_ID(N'dbo.vwGetCustomerInfo') 
                      AND xtype=N'V')
	BEGIN
		PRINT 'View Exists'
	END

RESULT:

[ALSO READ] :
How to check if a Database exists
How to check if a Table exists
How to check if a Stored Procedure exists in Sql Server
How to check if Temp table exists
How to check if a record exists in table