Drop all Views in database in SQL Server 2008

In this article I describe how to drop all views from a given database
  • 5349

Introduction

In this article I describe how to drop all the Views from a particular database.

View

View in SQL is a virtual table and this table is created on the result set of SQL statement. View is similar to a normal table and also have rows and columns. View may be combination  of some rows and columns of two or more than two tables.  You can drop all views from a particular database in SQL Server 2008. To drop all views from database see following example:

A Statement that create vendors table

create table vendors

 (

 vendorid int,

 vendorname varchar(15),

 vendorcity varchar(15),

 vendorstate varchar(15)

 )

Statements that insert data in vendors table

insert into vendors values (20,'vipendra','noida','up')

insert into vendors values (21,'deepak','lucknow','up')

insert into vendors values (22,'rahul','kanpur','up')

insert into vendors values (23,'malay','delhi','delhi')

insert into vendors values (24,'mayank','noida','up')

A Statement that is used to fetch data from vendors table

select * from vendors

Output:

table.jpg

Create two Views view1 and view2

Creation of first View:

create view view1

as

select * from vendors

go

select * from view1

 

Output:

table.jpg

Creation of second View:

create view view2

as

select vendorid from vendors

go

select * from view2

Output:

view2-in-sql.jpg

Create a cursor to drop all views from vendors table:

declare cur_dropView cursor

scroll for

select [name] from sysobjects where xtype='v'

open dropView_cursor

go

Declare @ViewName varchar(500)

fetch first from dropView_cursor into @ViewName

while @@fetch_status=0

begin

Exec('drop view ' + @ViewName)

fetch next from dropView_cursor into @ViewName

end

go

close dropView_cursor

go

deallocate  dropView_cursor

 

Output:

output-in-sql.jpg

© 2020 DotNetHeaven. All rights reserved.