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.

Up Next
    Ebook Download
    View all
    Learn
    View all