Skip to main content

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

Popular posts from this blog

Add day to ISODate in MongoDB

We can use $add operator to add days in ISODate in mongodb, $add is the Arithmetic Aggregation Operator which adds number and date in mongodb.
Syntax:

{ $add: [ <expression1>, <expression2>, ... ] }

Note:  If one of the argument is date $add operator treats to other arguments as milliseconds to add to the date.
Example: Suppose we have a Test collection as below.

{"Title" : "Add day to ISODate in MongoBD","CreatedDate" : ISODate("2016-07-07T08:00:00.000Z")}

Query to add 2 days in CreatedDate

db.Test.aggregate([      { $project: { Title: 1, AddedDate: { $add: [ "$CreatedDate", 2*24*60*60000 ] } } }    ])

Result:

{ "_id" : ObjectId("579a1567ac1b3f3732483de0"), "Title" : "Add day to ISODate in MongoBD", "AddedDate" : ISODate("2016-07-09T08:00:00.000Z") }

Note: As mentioned in above note we have to convert days in millisecond because $add operator treat to other arg…

Remove special characters from string in SQL server

I faced many times an issue to remove special characters from a string. Suppose you are working on searching concept and you have to remove the special characters from search string due to query performance, there are many solution are available but T-SQL is easily resolved this issue.
Following query may help you to resolve your issue.

DECLARE@strVARCHAR(400) DECLARE@expresVARCHAR(50)='%[~,@,#,$,%,&,*,(,),.,!]%' SET@str='(remove) ~special~ *characters. from string in sql!' WHILEPATINDEX(@expres,@str)> 0 BEGIN SET@str=Replace(REPLACE(@str,SUBSTRING(@str,PATINDEX(@expres,@str), 1 ),''),'-',' ') END SELECT@str