Link to home
Start Free TrialLog in
Avatar of Patrick Matthews
Patrick MatthewsFlag for United States of America

asked on

Drop a view if it exists

Insanely easy points out there for you T-SQL jockeys...

I have a view which I am implementing, and the CREATE VIEW statement works exactly as expected.

Before running the CREATE VIEW statement, I want to check for existence of that same view, and if it exists, to drop the view.  So, the logic looks like this:


USE MyDB

GO

IF EXISTS (statement to find the view) DROP VIEW dbo.MyView

GO

CREATE VIEW dbo.MyView AS <SQL statement>

Open in new window



What I do not have is the "statement to find the view" bit :)

Thanks!
ASKER CERTIFIED SOLUTION
Avatar of sunezapa
sunezapa

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
if not exists (select * from sysobjects
  where name='view-name' and xtype='V')
    exec ('create view view-name as  ...')

if exists (select * from sys.objects where [object_id] = object_id('dbo.MyView ) )
begin
 DROP VIEW dbo.MyView
end

CREATE VIEW dbo.MyView AS <SQL statement>

Open in new window

Avatar of James Murrell
i use


USE MyDB
GO

IF EXISTS(SELECT * FROM sys.views
WHERE name = 'StudentGrades' AND schema_id = SCHEMA_ID('dbo'))
DROP VIEW dbo.MyView
GO

CREATE VIEW dbo.MyView AS <SQL statement>
GO

Open in new window

in fact how about
USE [MyDB]
GO

IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[MyView]'))
DROP VIEW [dbo].[MyView]
GO

USE [MyDB]
GO


CREATE VIEW dbo.MyView AS <SQL statement>
GO

Open in new window

Avatar of Patrick Matthews

ASKER

Thanks!