统计一个数据库内所有表的记录条数总数出错
统计一个数据库内所有表的记录条数总和出错
如题,我自己写了下面的代码,但是运行没有得到正确数据。我的逻辑错在哪里呢?
请各位指点一下,不胜感激!
------解决方案--------------------
------解决方案--------------------
set @t=@t+@no
update #tb_count set tb_no=tb_no+@t
这里是不是重复累加了?
------解决方案--------------------
set @no='0'
set @sql='select @no=count(*) from '+@tb_name
exec sp_executesql @sql,N'@no int',@no output
------解决方案--------------------
------解决方案--------------------
------解决方案--------------------
7楼的是比较单纯的,我最后给你那个是统计比较准确的。
如题,我自己写了下面的代码,但是运行没有得到正确数据。我的逻辑错在哪里呢?
create table #tb_count (tb_no int not null)
declare @no int
declare @t int
set @t='0'
declare @sql nvarchar(100)
set @sql=''
insert into #tb_count values('0')
declare getname cursor scroll for select name from sysobjects where xtype='u'
declare @tb_name nvarchar(35)
open getname
fetch first from getname into @tb_name
while @@FETCH_STATUS=0
begin
set @no='0'
set @sql='select count(*) from '+@tb_name
exec @no=sp_executesql @sql
set @t=@t+@no
update #tb_count set tb_no=tb_no+@t
fetch next from getname into @tb_name
end
select * from #tb_count
close getname
deallocate getname
请各位指点一下,不胜感激!
------解决方案--------------------
EXEC sp_MSforeachtable @command1="print '?'",
@command2="sp_spaceused '?'",
@command3= "SELECT count(*) FROM ? "
------解决方案--------------------
set @t=@t+@no
update #tb_count set tb_no=tb_no+@t
这里是不是重复累加了?
------解决方案--------------------
set @no='0'
set @sql='select @no=count(*) from '+@tb_name
exec sp_executesql @sql,N'@no int',@no output
------解决方案--------------------
SELECT OBJECT_NAME(id) AS [TBName],MAX(rowcnt)[rowcounts] FROM sys.sysindexes
GROUP BY OBJECT_NAME(id)
------解决方案--------------------
SELECT OBJECT_SCHEMA_NAME(ddps.object_id) + '.' + OBJECT_NAME(ddps.object_id) AS name ,
SUM(ddps.row_count) AS row_count
FROM sys.dm_db_partition_stats AS ddps
JOIN sys.indexes ON indexes.object_id = ddps.object_id
AND indexes.index_id = ddps.index_id
WHERE indexes.type_desc IN ( 'CLUSTERED', 'HEAP' )
AND OBJECTPROPERTY(ddps.object_id, 'IsMSShipped') = 0
GROUP BY ddps.object_id
------解决方案--------------------
7楼的是比较单纯的,我最后给你那个是统计比较准确的。