22,300
社区成员




declare @dt1 datetime,@dt2 datetime
declare @cellname varchar(10)
declare @sql nvarchar(max)
--设置起止日期
set @dt1='2012-02-01'
set @dt2='2012-02-07'
--设置小区名
set @cellname='Acell'
--形成动态查询语句
select @sql=isnull(@sql+' union all ','')
+'select * from Table_'
+right(convert(varchar(8),dateadd(d,number,@dt1),112),6)
+' where cellname='''+@cellname+''''
from master..spt_values
where type='p' and dateadd(d,number,@dt1)<=@dt2
--执行动态查询语句
exec(@sql)
/*
如果将上面这句改成 select @sql,则你会得到这样的字串(复制网格中的内容):
select * from Table_120201 where cellname='Acell' union all select * from Table_120202 where cellname='Acell' union all select * from Table_120203 where cellname='Acell' union all select * from Table_120204 where cellname='Acell' union all select * from Table_120205 where cellname='Acell' union all select * from Table_120206 where cellname='Acell' union all select * from Table_120207 where cellname='Acell'
这就是你要的遍历各表的查询语句
*/
declare @tableName varchar(max)
declare @cursor cursor
set @cursor=cursor for
首先找出数据库中有多少个表中有这个字段!
select object_name(object_id) from sys.columns where [name]='Acell'
--[name]='Acell' 这里是指列的名称,不是指数据!
--打开游标
open @cursor
fetch next from @cursor into @tableName
while(@@fetch_status=0)
begin
exec ('update ['+@tableName+'] set cl='更新的内容' where [datetime] between 2012-02-07 and 2012-02-10')
fetch next from @cursor into @tableName
end
close @cursor
deallocate @cursor
declare @cursor cursor
set @cursor=cursor for
首先找出数据库中有多少个表中有这个字段!
select object_name(object_id) from sys.columns where [name]='Acell'
--打开游标
open @cursor
fetch next from @cursor into @tableName
while(@@fetch_status=0)
begin
exec ('update ['+@tableName+'] set cl='更新的内容' where [datetime] between 2012-02-07 and 2012-02-10')
fetch next from @cursor into @tableName
end
close @cursor
deallocate @cursor
declare @cursor cursor
set @cursor=cursor for
首先找出数据库中有多少个表中有这个字段!
select object_name(object_id) from sys.columns where [name]='Acell’
--打开游标
open @cursor
fetch next from @cursor into @tableName
while(@@fetch_status=0)
begin
exec ('update ['+@tableName+'] set cl='更新的内容' where [datetime] between 2012-02-07 and 2012-02-10')
fetch next from @cursor into @tableName
end
close @cursor
deallocate @cursor
--根据查询的列创建一个临时表
create table table_111201(id int)
insert into table_111201 select 1
go
create table table_111202(id int)
insert into table_111201 select 2
go
create table #temp(id int) -- 具体的字段
declare @start datetime
declare @end datetime
declare @sql varchar(8000)
set @start = '2011-12-01'
set @end = '2011-12-02'
select @sql = isnull(@sql+' union all ','')+'select * from table_'+date
from(
select right(convert(varchar(8),dateadd(dd,number,@start),112),6) date
from master..spt_values
where [type] = 'p' and number between 0 and datediff(dd,@start,@end)
)t
insert into #temp exec(@sql)
select * from #temp
--select 其他查询,根据#temp临时表
drop table #temp,table_111201,table_111202
/***************
id
-----------
1
2
(2 行受影响)