In SQL SERVER 2005, perhaps this will work :
-- variable to object name
declare @name varchar(100)
@name varchar(100) -- variable to hold object type
declare @xtype char(1)
@xtype char(1) -- variable to hold sql string
declare @sqlstring nvarchar(1000)
@sqlstring nvarchar(1000) declare SPViews_cursor cursor for
SPViews_cursor cursor for /**
SELECT sysobjects.name, sysobjects.xtype
FROM sysobjects
join sysusers on sysobjects.uid = sysusers.uid
where OBJECTPROPERTY(sysobjects.id, N'IsProcedure') = 1
or OBJECTPROPERTY(sysobjects.id, N'IsView') = 1 and sysusers.name =
'USERNAME'
**/
SELECT ROUTINE_NAME AS NAME, 'P' AS XTYPE from INFORMATION_SCHEMA.ROUTINES
ROUTINE_NAME AS NAME, 'P' AS XTYPE from INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_BODY = 'SQL'
ROUTINE_BODY = 'SQL' ORDER BY ROUTINE_NAME
BY ROUTINE_NAME open SPViews_cursor
SPViews_cursor fetch next from SPViews_cursor into @name, @xtype
next from SPViews_cursor into @name, @xtype while @@fetch_status = 0 begin
begin
@@fetch_status = 0 begin
begin -- test object type if it is a stored procedure
if @xtype = 'P'
if @xtype = 'P' begin
begin set @sqlstring = 'drop procedure ' + @name exec sp_executesql @sqlstring set @sqlstring = ' '
set @sqlstring = ' '
exec sp_executesql @sqlstring set @sqlstring = ' '
set @sqlstring = ' '
set @sqlstring = 'drop procedure ' + @name exec sp_executesql @sqlstring set @sqlstring = ' '
set @sqlstring = ' '
exec sp_executesql @sqlstring set @sqlstring = ' '
set @sqlstring = ' ' end
end -- test object type if it is a view
if @xtype = 'V'
if @xtype = 'V' begin
begin set @sqlstring = 'drop view ' + @name exec sp_executesql @sqlstring set @sqlstring = ' '
set @sqlstring = ' '
exec sp_executesql @sqlstring set @sqlstring = ' '
set @sqlstring = ' '
set @sqlstring = 'drop view ' + @name exec sp_executesql @sqlstring set @sqlstring = ' '
set @sqlstring = ' '
exec sp_executesql @sqlstring set @sqlstring = ' '
set @sqlstring = ' ' end
end -- get next record
fetch next from SPViews_cursor into @name, @xtype end close SPViews_cursor
close SPViews_cursor
end close SPViews_cursor
close SPViews_cursor
fetch next from SPViews_cursor into @name, @xtype end close SPViews_cursor
close SPViews_cursor
end close SPViews_cursor
SPViews_cursor deallocate SPViews_cursor
SPViews_cursor