先将表找出来放入临时表#table1(name)
insert into #table1
select name from sysobjects where type='U' and crdate=dateadd(day,-15,getdate()) and name like 't%'select 'drop table '+name from #table1然后将生成的drop语句在查询分析器里执行就可以了
insert into #table1
select name from sysobjects where type='U' and crdate=dateadd(day,-15,getdate()) and name like 't%'select 'drop table '+name from #table1然后将生成的drop语句在查询分析器里执行就可以了
比如
DECLARE @TableName Varchar(20)
DECLARE TableCursor Cursor For
SELECT name FROM Sysobjects WHERE xtype='U' AND
crdate<DATEADD(d,15,getdate()) AND name LIKE 't%'
FOR READ ONLY
OPEN TableCursor
FETCH FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS=0
BEGIN
EXEC 'DROP TABLE '+@TableName
FETCH NEXT FROM TableCursor INTO @TableName
END
CLOSE TableCursor
DEALLOCATE TableCursor
语法错误:
Line 10: Incorrect syntax near 'DROP TABLE '.
set @tableNames = ''
select @tableNames = @tableNames + ',' + name from sysobjects where xtype = 'U' and crdate<DATEADD(d,15,getdate()) AND name LIKE 't%'
exec ('drop table '+right(@tableNames,len(@tableNames)-1))
DECLARE TableCursor Cursor For
SELECT name FROM Sysobjects WHERE xtype='U' AND
crdate<DATEADD(d,15,getdate()) AND name LIKE 't%'
FOR READ ONLY
OPEN TableCursor
FETCH FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS=0
BEGIN
EXEC ('DROP TABLE '+@TableName)
FETCH NEXT FROM TableCursor INTO @TableName
END
CLOSE TableCursor
DEALLOCATE TableCursor
Incorrect syntax near the keyword 'right'.
应该是dateadd(day,-15,getdate())
ex 今天是3月1日,我要删除2月15日之前的数据
楼上几位取的不对!
zhuzhichao(炎龙骑士团—索尔—破龙击)的方法更好,我已经试过了。
to jxwa_wzw(加分吧) :
zhuzhichao的方法出错,应该是没有找到相应表记录的原因,也就是那个日期的负号少了。你可以加上相应的出错处理。总是粗心大意,唉,何时了!
set @tableNames = ''
select @tableNames = @tableNames + ',' + name from sysobjects where xtype = 'U' and crdate<DATEADD(d,-15,getdate()) AND name LIKE 't%'
exec ('drop table '+substring(@tableNames,1,len(@tableNames)-1))
set @tableNames = ''
select @tableNames = @tableNames + ',' + name from sysobjects where xtype = 'U' and crdate<DATEADD(d,-15,getdate()) AND name LIKE 't%'
exec ('drop table '+substring(@tableNames,2,len(@tableNames)-1))
如果用substring的話,就不要將字符串的長度-1了.
最後一句應該是:exec ('drop table '+substring(@tableNames,2,len(@tableNames)))