A bit about

Hello, everyone! All you can see below is just my bank of information. Some material I've found in the fathomless net, some I've learned myself. Don't think all of the information here is right or actual, but may be it could be of use for you :) All feedback is welcome, especially constructive ones :)

Sunday, November 22, 2009

MS SQL: Drop all views

When updating database it's sometimes hard to track all minor changes in lots of tables, views and other database elements. In case of tables, imho, we don't have lots of options and we are to track all changes carefully (not to damage data). On the other hand views, functions and stored procedures don't contain any data and we have more freedom.

That's why I used to drop and create all views when updating them. It's not too slow and scripts are not very large. Also, MS SQL Server can generate one script for creation of all views automatically and as result we have just a complete snapshot of them.

The problem is that before applying script on another server, we have to delete all previous views. There is no a built-in procedure to do this and I found a script and modified it a little bit:
declare @name varchar(100)
declare @sqlstring nvarchar(1000)

declare SPViews_cursor cursor for
SELECT sysobjects.name
FROM sysobjects
where OBJECTPROPERTY(sysobjects.id, N'IsView') = 1

open SPViews_cursor

fetch next from SPViews_cursor into @name

while @@fetch_status = 0
begin
set @sqlstring = 'drop view ' + @name
exec sp_executesql @sqlstring
set @sqlstring = ' '
fetch next from SPViews_cursor into @name
end

close SPViews_cursor
deallocate SPViews_cursor