|
|
|
|
|
|
|
declare @tb varchar(100)
if exists(select * from sysobjects where xtype='U' and datediff(day,crdate,getdate())=0) begin select @tb=name from sysobjects where xtype='U' and datediff(day,crdate,getdate())=0 exec('drop table '+@tb) end |
|
|
|
delete from 表 where date = getdate()
|
|
|
|
declare @tblname varchar(30)
declare cursorName cursor for Select Name from sysobjects where Xtype='U' and datediff(day,crdate,getdate())=0 open cursorName fetch next from cursorName into @tblname while @@fetch_status=0 begin exec('drop table '+@tblname) fetch next from cursorName into @tblname end close cursorName deallocate cursorName |
|
|
|
--查询出今天创建的表
select name from sysobjects where datediff(day,crdate,getdate())>0 and xtype='u' and status>=0 |
|
|
|
to pbsql(风云):
当有很多条时，我用 eclare @t varchar,@n int select @n=count(*) from sysobjects where xtype='u' and datediff(day,crdate,getdate())=0 while @n>0 begin select @t=name from sysobjects where xtype='U' and datediff(day,crdate,getdate())=0 exec('drop table '+@t) set @n=@n-1 end 为什么不对？ |
|
|
|
up
|
|
|
|
如果你数据库的排序规则不区分大小写，那段程序是对的，否则就不对了（注意'u'、'U'）
|
|
|
|
use yourdb
go select name from sysobjects where xtype = 'u' and status >= 0 --查看用户表 and datediff(day,crdate,getdate()) = 0 --筛选出今天用户表 |
|
|
|
to to pbsql(风云):
但还是不对，我用下面的代码测试： declare @t varchar,@n int select @n=count(*) from sysobjects where xtype='U' and datediff(day,crdate,getdate())=0 while @n>0 begin select name from sysobjects where xtype='U' and datediff(day,crdate,getdate())=0 set @n=@n-1 end 时看见结果都是是我想要的表名；但用下面的代码时 eclare @t varchar,@n int select @n=count(*) from sysobjects where xtype='u' and datediff(day,crdate,getdate())=0 while @n>0 begin select @t=name from sysobjects where xtype='U' and datediff(day,crdate,getdate())=0 select @t set @n=@n-1 end 结果都变成了'R',真的搞不懂为什么？ |
|
|
|
select name from sysobjects
where datediff(day,crdate,getdate())>0 and xtype='u' and status>=0 |
|
|
|
低级错误：@t没有定义长度
|
|
|
|
用
declare @t varchar(100),@n int select @n=count(*) from sysobjects where xtype='U' and datediff(day,crdate,getdate())=0 while @n>0 begin select name from sysobjects where xtype='U' and datediff(day,crdate,getdate())=0 set @n=@n-1 end 还是一样呀！！！！！！！ |
|
|
|
用上面的代码是结果全为'Rate',谁能不能在自己的机子上试一下，看结果怎么样？
|
|
|
|
我想我现在知道错在哪了？select name from sysobjects where xtype='U' and datediff(day,crdate,getdate())=0
结果本来就是一个集合，是不能付值给一个变量． |
|
|
|
你错了：
declare @t varchar(100),@n int select @n=count(*) from sysobjects where xtype='U' and datediff(day,crdate,getdate())=0 while @n>0 begin select @t=name from sysobjects where xtype='U' and datediff(day,crdate,getdate())=0 exec('drop table '+@t) set @n=@n-1 end |
|
|
|
to to pbsql(风云):
非常感谢，你是对的，但我还有一点不明白就是下面的代码结果为什么是许多条一样的结果？ declare @t varchar(100),@n int select @n=count(*) from sysobjects where xtype='U' and datediff(day,crdate,getdate())=0 while @n>0 begin select @t=name from sysobjects where xtype='U' and datediff(day,crdate,getdate())=0 select @t set @n=@n-1 end 是不是 select @t=name from sysobjects where xtype='U' and datediff(day,crdate,getdate())=0 这句话每次选出来的是最前面的一个？ |
|
|
|
是的
|
|
|
关闭