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