--一次性列出数据库中所有表的表名和记录数目 select o.name,i.rows from sysobjects o,sysindexes i where o.xtype='U' and o.Id=i.Id and i.Indid<2 order by o.name
大致应该是这样,没做过测试,楼主修改下declare @DTBeg Datetime, @DTEnd Datetime, @DT DateTime, @sql varchar(500) set @DTBeg = '2011-3-10' set @DTEnd = '2011-3-15' set @DT = @DTBeg set @sql = 'select * from [' + CONVERT(varchar(10), @DT, 120) + ']' -- yyyy-mm-dd格式 set @DT = DateAdd(dd, 1, @DT) while @DT <= @DTEnd begin if exists(select * from sysobjects where name = '''' + CONVERT(varchar(10), @DT, 120) + '''') set @sql = @sql + ' union all select * from [' + CONVERT(varchar(10), @DT, 201) + ']' set @DT = DateAdd(dd, 1, @DT) end exec(@sql)
刚才没看到表格图片,修正下declare @DTBeg Datetime, @DTEnd Datetime, @DT DateTime, @sql varchar(500) set @DTBeg = '2011-3-10' set @DTEnd = '2011-3-15' set @DT = @DTBeg set @sql = 'select * from L' + CONVERT(varchar(10), @DT, 112) -- yyyy-mm-dd格式 set @DT = DateAdd(dd, 1, @DT) while @DT <= @DTEnd begin if exists(select * from sysobjects where name = 'L' + CONVERT(varchar(10), @DT, 112)) set @sql = @sql + ' union all select * from L' + CONVERT(varchar(10), @DT, 112) set @DT = DateAdd(dd, 1, @DT) end exec(@sql)
我一般只看到一个月的业务数据会单独放一张表的按你建表的规则,用动态SQL语句拼出来就OK了
select o.name,i.rows
from sysobjects o,sysindexes i
where o.xtype='U'
and o.Id=i.Id
and i.Indid<2
order by o.name
set @DTBeg = '2011-3-10'
set @DTEnd = '2011-3-15'
set @DT = @DTBeg
set @sql = 'select * from [' + CONVERT(varchar(10), @DT, 120) + ']' -- yyyy-mm-dd格式
set @DT = DateAdd(dd, 1, @DT)
while @DT <= @DTEnd begin
if exists(select * from sysobjects where name = '''' + CONVERT(varchar(10), @DT, 120) + '''')
set @sql = @sql + ' union all select * from [' + CONVERT(varchar(10), @DT, 201) + ']'
set @DT = DateAdd(dd, 1, @DT)
end
exec(@sql)
set @DTBeg = '2011-3-10'
set @DTEnd = '2011-3-15'
set @DT = @DTBeg
set @sql = 'select * from L' + CONVERT(varchar(10), @DT, 112) -- yyyy-mm-dd格式
set @DT = DateAdd(dd, 1, @DT)
while @DT <= @DTEnd begin
if exists(select * from sysobjects where name = 'L' + CONVERT(varchar(10), @DT, 112))
set @sql = @sql + ' union all select * from L' + CONVERT(varchar(10), @DT, 112)
set @DT = DateAdd(dd, 1, @DT)
end
exec(@sql)