How to check if a VIEW exist in SQL Server

Many scenarios come in query writing, we have to check particular VIEW exists in the database or not. There are many approaches to check the existing view in the 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 exists in the current database for all schema.


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


Result
View exists

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


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


Result
View exists

Using sys.objects catalog
Using the following script, you can also check to exist of view "EmployeeView" in the database.


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



Result
View exist

Using sys.sql_modules catalog
We can also use sys.sql_modules catalog to check the existing view "EmployeeView" using the 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 exists.'
END
ELSE
BEGIN
       PRINT 'View does not exist.'
END


Result
View exists

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


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


Result
View exist

PostgreSQL-Query: Sort result set by specific field values using ORDER BY Clause

Problem: Suppose we have a book_inventory table which has some columns such as id, isbn, title, author, publisher, publish_date, etc.. whe...