做个游标不久可以了吗,读所有的表,并且表中有字段sDate(假如你的时间字段为他)select b.name as tblname,a.name as fldname syscolumns a ,sysobjects b where a.id=b.id and b.xtype='u' and a.name='sDate'
/************************************************************/ /*功能:批量删除表*/ /*定义游标*/ /************************************************************/ DECLARE @TABLENAME VARCHAR(50),@SQL1 VARCHAR(500) DECLARE Cur_DropTable CURSOR FOR SELECT name FROM sysobjects WHERE Upper(Type) = 'U' and name like 'tmp%' or name like 'zhx#%' ORDER BY nameOPEN Cur_DropTableFETCH NEXT FROM Cur_DropTable INTO @TABLENAME /*循环执行*/ WHILE @@FETCH_STATUS = 0 BEGIN SET @SQL1 = ' drop TABLE ' + @TABLENAME EXECUTE(@SQL1) PRINT '表:'+ @TABLENAME + '删除成功......' FETCH NEXT FROM Cur_DropTable INTO @TABLENAME ENDCLOSE Cur_DropTable DEALLOCATE Cur_DropTable GO lz先看看,有些要改一下,这个是删除一些有规律的表。
where a.id=b.id and b.xtype='u' and a.name='sDate'
/*功能:批量删除表*/
/*定义游标*/
/************************************************************/
DECLARE @TABLENAME VARCHAR(50),@SQL1 VARCHAR(500)
DECLARE Cur_DropTable CURSOR FOR
SELECT name
FROM sysobjects
WHERE Upper(Type) = 'U' and name like 'tmp%' or name like 'zhx#%'
ORDER BY nameOPEN Cur_DropTableFETCH NEXT FROM Cur_DropTable
INTO @TABLENAME
/*循环执行*/
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQL1 = ' drop TABLE ' + @TABLENAME
EXECUTE(@SQL1)
PRINT '表:'+ @TABLENAME + '删除成功......'
FETCH NEXT FROM Cur_DropTable
INTO @TABLENAME
ENDCLOSE Cur_DropTable
DEALLOCATE Cur_DropTable
GO
lz先看看,有些要改一下,这个是删除一些有规律的表。