Drop all Views in database in SQL Server 2008
Posted in
SQL | Views December 26, 2019
In this article I describe how to drop all views from a given database
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:
Create two Views view1 and view2
Creation of first View:
create view view1
as
select * from vendors
go
select * from view1
Output:
Creation of second View:
create view view2
as
select vendorid from vendors
go
select * from view2
Output:
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: