Deleting the views

[manmaria]

I like to delete the views if exists and satisfy a condition. For example if I have a view like LoadInvView or LoadPOView or Load%View then drop them. How do I accomplish this? I think I might have to use a cursor and ExecuteImmediate but I am not sure. Personally I do not like cursors.

TIA.

 

[RiverGuy]

Delete what views?

 

[manmaria]

User views

 

[RiverGuy]

What is something in common to the views that pertains to only those views you wish to drop? It would be a bad idea to write a script to go through a database and drop every single view in case you want some of them to persist.

 

[manmaria]

It is like a change in the view. When ever there is a change in certain views, we drop all those views related to a module and recreate them. So I am not sure what is wrong in that. At the same time, I am recreating those views with the changes in the same Procedure. So, What I want is how do I drop certain kind of views.

 

[jbenson001]

Perhaps someone can come up with something more elegant, but I would try something like this:

--Get all views into a temp table in  your DB.
Select * INTO #Views
 from INFORMATION_SCHEMA.VIEWS
Where TABLE_NAME LIKE 'Your Criteria Here'

--Here, use a WHILE loop to delete each view in your 
--#temp table.

 

[manmaria]

OK. I tried this but I think it is executing only the first resultant set and throwing the errors like the

"
Cannot drop the view 'FirstView', because it does not exist or you do not have permission."

This view is been deleted but rest of the views are not.
====
select distinct t.table_name INTO #vIEWS
From Information_Schema.Tables t inner join sys.schemas s
on s.name=t.table_schema
where t.table_type='View' and S.name=N'TEST'
and table_name NOT like '%TESTView' and
table_name not like '%LASTView' and
table_name not like '%ABView' and
table_name not like '%CSVIEW%'
DECLARE @sViewName VARCHAR(100), @sql varchar(512)


While (select COUNT(*) from #Views) >0
BEGIN
select @sviewname= table_name from #views
set @sql='drop view '+ @sviewname
exec(@sql)
end

 

[manmaria]

Got it.

select distinct t.table_name INTO #vIEWS
From Information_Schema.Tables t inner join sys.schemas s
on s.name=t.table_schema
where t.table_type='View' and S.name=N'TEST'
and table_name NOT like '%TESTView' and
table_name not like '%LASTView' and
table_name not like '%ABView' and
table_name not like '%CSVIEW%'
DECLARE @sViewName VARCHAR(100), @sql varchar(512)


While (select COUNT(*) from #Views) >0
BEGIN
select @sviewname= table_name from #views
set @sql='drop view '+ @sviewname
exec(@sql)
fetch next from curview into @sviewname ---added this
end

 

[gmmastros]

Try it like this:

Create Table #Views(RowId Int Identity(1,1), Table_Name VarChar(300))

Insert Into #Views(Table_Name)
select distinct t.table_name 
 From   Information_Schema.Tables  t inner join sys.schemas s
on s.name=t.table_schema
where t.table_type='View' and S.name=N'TEST'
and table_name NOT like '%TESTView' and
   table_name not like '%LASTView' and
   table_name not like '%ABView' and
   table_name not like '%CSVIEW%'  
DECLARE @sViewName VARCHAR(100), @sql varchar(512)
 
Declare @Max Int
Declare @i int

Select @Max = Max(RowId), @i = 1
From   #Views

While @i <= @Max
BEGIN
	
   select @sviewname= table_name from #views Where RowId = @i
   set @sql='drop view '+  @sviewname
   exec(@sql)
   set @i = @i + 1
end 

Drop Table #Views

The idea here is... we add an identity column to the temp table and then use it to select the correct row from the temp table inside the while loop.

Make sense?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[manmaria]

Oops. Sorry, wrong post. I was trying using cursor and also while loop with temp table.

Using cursor is working now.

DECLARE @sViewName VARCHAR(100), @SQL VARCHAR(512);
declare curView --DYNAMIC SCROLL
CURSOR FOR

select distinct t.table_name From Information_Schema.Tables t inner join sys.schemas s
on s.name=t.table_schema
where t.table_type='View' and S.name=N'TEST'
and table_name NOT like '%TESTView' and
table_name not like '%LASTView' and
table_name not like '%ABView' and
table_name not like '%CSVIEW%'

open curview
FETCH NEXT FROM CURVIEW INTO @SVIEWNAME
WHILE (@@FETCH_STATUS = 0)
BEGIN
SET @SQL='DROP VIEW ' + @SVIEWNAME
EXECUTE (@SQL)
fetch next from curview into @sviewname
END
CLOSE CURVIEW
DEALLOCATE CURVIEW
GO