[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

delete sql views in bulk

Posted on 2011-10-26
5
Medium Priority
?
337 Views
Last Modified: 2012-05-12
I need to delete many, but not all, of my sql views collectively, rather than one a time.  Someone showed me that this can be done in SQL Server Management Studio, but I don't remember what he did.  Does anyone know how to do this?  Thank you in advance.
0
Comment
Question by:ginsburg7
  • 2
  • 2
5 Comments
 
LVL 15

Accepted Solution

by:
AmmarR earned 2000 total points
ID: 37036401
hi

you can use object explorer details

on the right pane click on the views and go to the menu click view --> object explorer details, you will list of all views on the right pane , select the views you want to delete and press delete
object-explorer.png
0
 

Expert Comment

by:VoiceOver
ID: 37036438
This should do it. Usual way to do it is through a cursor but performance wise I prefer a while loop:

DECLARE @MaxViewID bigint
DECLARE @CurViewID bigint
DECLARE @PrevViewID bigint
DECLARE @ViewName varchar(500)

SELECT
      @MaxViewID =  MAX([Object_id])
FROM
      sys.objects
WHERE
      Type = 'v'

SET      @PrevViewID = 1
SET @CurViewID = 1

WHILE @CurViewID < @MaxViewID
      BEGIN
            SELECT
                  @CurViewID = MIN(O.object_id)
            FROM
                  sys.objects O
            INNER JOIN
                  sys.schemas S
            ON
                  O.schema_id = S.schema_id
            WHERE
                  [Type] = 'v'
            AND
                  O.[Object_id] > @PrevViewID

            SELECT
                  @ViewName = S.[Name] + '.[' + O.[Name] + ']'
            FROM
                  sys.objects O
            INNER JOIN
                  sys.schemas S
            ON
                  O.schema_id = S.schema_id
            WHERE
                  O.[Object_id] = @CurViewID

            EXEC('DROP VIEW ' + @ViewName)
            PRINT 'Dropped: ' + @ViewName
            SET @PrevViewID = @CurViewID      
      END
0
 

Author Closing Comment

by:ginsburg7
ID: 37037286
Perfect.  That's just what I needed.  Thank you.
0
 
LVL 15

Expert Comment

by:AmmarR
ID: 37040687
most welcome
0
 

Expert Comment

by:VoiceOver
ID: 37043052
Sorry, didn't realize you wanted to do it through the interface; realize my solution was a bit overkill. :)
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Suggested Courses

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question