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