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 :)
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.
To drop a single view by name:
To drop all views in a database:
create procedure sp_Drop_View (IN @viewName varchar(128) DEFAULT 'NONE')
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;
if exists(select 1 from sys.systable where table_name=@viewName and table_type='VIEW') then
EXECUTE IMMEDIATE 'DROP VIEW ' || @viewName;
23 Feb '10, 22:02