mssqlserver 删除包含指定字符串的数据表或视图

删除表格

CREATE PROCEDURE dbo.PROC_DELTABLE 

AS 
DECLARE @TABLENAME AS NVARCHAR(128) 
DECLARE cur_del CURSOR FOR
select name from sysobjects where type='U' and name like '指定的字符串%'
OPEN cur_del 
FETCH NEXT FROM cur_del INTO @TABLENAME 
WHILE(@@FETCH_STATUS=0) 
BEGIN 
PRINT 'drop table '+@TABLENAME 
EXEC('drop table '+@TABLENAME) 
FETCH NEXT FROM cur_del INTO @TABLENAME 
END 
CLOSE cur_del 
DEALLOCATE cur_del 
GO

EXEC PROC_DELTABLE

删除视图

CREATE PROCEDURE dbo.PROC_VIEW
AS 
DECLARE @VIEWNAME AS NVARCHAR(128) 
DECLARE cur_del CURSOR FOR
select name from sysobjects where xtype='v' and name like '指定的字符串%'
OPEN cur_del 
FETCH NEXT FROM cur_del INTO @VIEWNAME 
WHILE(@@FETCH_STATUS=0) 
BEGIN 
PRINT 'drop view '+@VIEWNAME  
EXEC('drop view '+@VIEWNAME ) 
FETCH NEXT FROM cur_del INTO @VIEWNAME 
END 
CLOSE cur_del 
DEALLOCATE cur_del 
GO

EXEC PROC_VIEW