Dropping All Views in Database in SQL Server 2012

Introduction

In this article I describe how to drop all the Views in a particular database. In my previous article I described how to remove all the Stored Procedures in a database. You can visit, Dropping all the stored Procedure in a database in SQL Server.

I assume you have a basic knowledge of Views and cursors; for more help you can visit:

Views in SQL Server 2012

Cursor in SQL Server 2012

First of all we create a table

Creation of Table:

create table emp(empId int, empName varchar(15))
go
insert into emp
select 1,'Deepak'union all
select 2,'Arora'
go
select * from emp

Output:



Now we Create two Views

Creation of first View:

create view v1
as
select * from emp
go
select * from v1

Output:



Creation of second View:

create view v2
as
select empId from emp
go
select * from v2

Output:



Now we create a Cursor :

declare cur_dropView cursor
scroll for
select [name] from sysobjects where xtype='v'

Now run the following code:

open cur_dropView
go
Declare @ViewName varchar(500)
fetch first from cur_dropView into @ViewName
while @@fetch_status=0
begin
Exec('drop view ' + @ViewName)
fetch next from cur_dropView into @ViewName
end
go
close cur_dropView
go
deallocate  cur_dropView

Output:



Now see the Views in the database:

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

Output:



Summary:

In this article I described how to drop all the Views in a database in SQL Server. I hope this article has helped you to understand this topic. Please share if you know more about this. Your feedback and constructive contributions are welcome.