Oct 22, 2015

How to check if a VIEW exist in SQL Server

Many scenario come in query writing, we have to check particular VIEW exists in database or not. There are many approach to check existing view in SQL server, some approach script described below.

For the demonstration I am creating a database "Codefari" same as table "Employee" and view "EmployeeView". Run below script.


USE[Codefari]
GO
CREATE TABLE Employee
(
       ID INT IDENTITY(1,1),
       FirstName VARCHAR(400),
       LastName VARCHAR(400)
)
GO
INSERT INTO Employee
SELECT 'Dilip','Singh'
UNION ALL
SELECT 'Ashish','Singh'
GO
CREATE VIEW dbo.EmployeeView
AS
SELECT ID, FirstName+' '+LastName AS FullName FROM Employee
GO
SELECT * FROM dbo.EmployeeView
GO


 Using sys.view catalog
We can write a query like below to check a particular VIEW exist in current database for all schema.


USE[Codefari]
GO
IF EXISTS (SELECT 1 FROM sys.views WHERE Name='EmployeeView')
BEGIN
       PRINT 'View is exist'
END
ELSE
BEGIN
       PRINT 'View is not exist'
END


Result
View is exist

Above script is responsible to check existing of view "EmployeView" for all schema in same database. If you want to check existing view for a particular schema in same database then use following script.


USE[Codefari]
GO
IF EXISTS (SELECT 1 FROM sys.views WHERE OBJECT_ID=OBJECT_ID('dbo.EmployeeView'))
BEGIN
       PRINT 'View is exist'
END
ELSE
BEGIN
       PRINT 'View is not exist'
END


Result
View is exist

Using sys.objects catalog
Using following script you can also check existing of view "EmployeeView" in database.


USE[Codefari]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID('dbo.EmployeeView') AND type = 'V')
BEGIN
    PRINT 'View is Exist'
END
ELSE
BEGIN
       PRINT 'View is not exist'
END



Result
View is exist

Using sys.sql_modules catalog
We can also use sys.sql_modules catalog to check existing view "EmployeeView" using following script.


USE[Codefari]
GO
IF EXISTS (SELECT 1 FROM sys.sql_modules WHERE object_id =  OBJECT_ID('dbo.EmployeeView') AND OBJECTPROPERTY(object_id, 'IsView') = 1)
BEGIN
    PRINT 'View is exist'
END
ELSE
BEGIN
       PRINT 'View is not exist'
END


Result
View is exist

Using OBJECT_ID() function
Using  OBJECT_ID() function we can check existing of view "EmployeeView" for same database. See following script.


USE[Codefari]
GO
IF OBJECT_ID(N'dbo.EmployeeView', N'V') IS NOT NULL
BEGIN
    PRINT 'View is exist'
END
ELSE
BEGIN
       PRINT 'View is not exist'
END


Result
View is exist