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:

Droping-all-view-in-sql-server.png

Now we Create two Views

Creation of first View:


create
view v1
as
select
* from emp
go

select
* from v1

Output:


Droping-all-view-in-sql-server.png

Creation of second View:


create
view v2
as
select
empId from emp
go

select
* from v2

Output:

Droping-all-view-in-sql-server-.png

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:

Droping-all-view-in-sql-server-2012.png

Now see the Views in the database:

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

Output:

Droping-all-view-in-sql-serverr.png

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.

Next Recommended Readings