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
