Dropping views in SQL Server

[fallenangel99]

hey all,

anyone have any idea how to drop ALL views in a SQL Server database for a given schema?

Here is what I got (from google)


select 'drop view [' + TABLE_SCHEMA + '].' + QUOTENAME
(TABLE_NAME)from INFORMATION_SCHEMA.VIEW

The above script lists all the views for that schema.. how do I drop them ALL at the same time rather than do 'drop view <view_name>' one by one?

Thanks.

 

[Mark R]

declare @temp table(viewname nvarchar(3000), id int identity(1,1))
declare @sql nvarchar(3000)
declare @num int
declare @index int

insert into @temp (viewname) select ('[' + TABLE_SCHEMA + '].' + QUOTENAME (TABLE_NAME)) FROM INFORMATION_SCHEMA.VIEWS

select @num = @@ROWCOUNT
select @index = @num

while (@index > 0)
begin
select @sql = 'DROP VIEW ' + (SELECT viewname FROM @temp WHERE @index = id)
print @sql
--exec (@sql)
select @index = @index -1
end

 

[fallenangel99]

I tried that and it looked like it dropped the views. but if I run select * from <view_name> I get information back.

Here is what I get after I run your script:

DROP VIEW [sysadm].[view_one]
DROP VIEW [sysadm].[view_two]
DROP VIEW [sysadm].[view_three]
DROP VIEW [sysadm].[view_four]


Many thanks for the script. I will play with it more and see if I can figure this out =)

Thanks again.

 

[fallenangel99]

nm got it. Thanks again Mark R.