--方法1 DECLARE @Table NVARCHAR(30)DECLARE tmpCur CURSOR FORSELECT name FROM sys.objects WHERE TYPE='U' AND name LIKE N'HSUPA%'OPEN tmpCurFETCH NEXT FROM tmpCur INTO @TableWHILE @@FETCH_STATUS = 0BEGIN    DECLARE @sql VARCHAR(100)    SELECT @sql = 'drop table ' + @Table    EXEC(@sql)    FETCH NEXT FROM tmpCur INTO @TableENDCLOSE tmpCurDEALLOCATE tmpCur

 

方法2

/*--------------------------------功能说明:  批量DropTable使用说明:  使用时一定要小心，因为删选表的where条件是like所有必须保证where后的like确定与你要删除表名相匹配---------------------------------*/--------参数定义-------------------DECLARE @tableName AS Nvarchar(50) --查询表名条件（小心！，确保like条件是你要Drop的表.TableName尽量精确）SET @tableName='test'  ----------------------------------------SELECT name FROM sys.tables   WHERE name LIKE '%'+@tableName+'%' --查询出要删除表的名称IF @tableName='' SET @tableName='tableName'--初始化TableName为tableName,防止@tableName为空DECLARE @tableNames AS Nvarchar(3000)DECLARE @sql AS Nvarchar(3000)SET @tableNames=(SELECT ','+name FROM sys.tables   WHERE name LIKE '%'+@tableName+'%'  FOR XML PATH(''))SET @tableNames= Stuff(@tableNames,1,1,'')SET @sql='DROP TABLE '+@tableNamesEXEC(@sql)