If this sample was helpful to you, vote it up.

If you have an improved version, or a closely related sample, post it as an answer.

If you have a question or complaint, post it as a comment (or an answer, if you have a lot to say :)


Description:
Know the view you want to drop, but don't know how to check for existence? Want to drop all views in the system, but have no automated way of doing it? Specify the view name as the parameter to drop only that view (IF it exists). Leave it empty to drop'em all.


Usage:
To drop a single view by name:

call sp_Drop_View('vw_People_Info');

To drop all views in a database:

call sp_Drop_View();

Code:

create procedure sp_Drop_View (IN @viewName varchar(128) DEFAULT 'NONE')
begin           
    IF @viewName = 'NONE' THEN
        FOR VIEW_NAME_FOR as VIEW_NAME_CURSOR dynamic scroll cursor for
                select st.table_name as VIEW_NAME
                  from systab st
                 where st.creator = 1 AND
                       st.table_type = 'VIEW'
              order by st.table_name asc do
            execute immediate 'DROP VIEW ' || VIEW_NAME;
        END FOR;
    ELSE
        if exists(select 1 from sys.systable where table_name=@viewName and table_type='VIEW') then
            EXECUTE IMMEDIATE 'DROP VIEW ' || @viewName;
        end if;
    END IF;
end;

asked 23 Feb '10, 22:02

Calvin%20Allen's gravatar image

Calvin Allen
1.5k232638
accept rate: 25%

edited 24 Feb '10, 19:47

Volker%20Barth's gravatar image

Volker Barth
31.3k312458674

Adding the owner name as a 2nd param (with default "DBA"?) would be nice. E.g. our schemata are usually owned by "dbo" - primarily for historical purposes like compatibility with MS SQL.

(24 Feb '10, 08:24) Volker Barth

I have used a similar proc in the past, but fortunately SA 11.0.1 introduced the following handy syntax:

DROP VIEW IF EXISTS [owner].viewname

(As it does for other database objects like triggers etc.)

But of course it does not allow to drop all views:)

permanent link

answered 24 Feb '10, 08:22

Volker%20Barth's gravatar image

Volker Barth
31.3k312458674
accept rate: 33%

edited 24 Feb '10, 19:46

That's fantastic! I wish we were on 11, but we only just went to 10 last year.

(24 Feb '10, 22:05) Calvin Allen
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×114
×25
×10

question asked: 23 Feb '10, 22:02

question was seen: 1,102 times

last updated: 24 Feb '10, 19:47