UC知道SQLSERVER2000统计数据库数据量
来源：百度知道 编辑：UC知道 时间：2024/06/04 16:03:23
我想写一过程来实现对数据库数据量的统计
declare @TableName varchar(100),@Count int
declare cur cursor for select table_name from information_schema.tables
create table #tmpTable (TblName nvarchar(100), DCount int)
open cur
fetch next from cur into @TableName
while @@fetch_status=0
begin
exec( ' select count(*) into ' + @Count + ' from ' + @TableName )
exec( 'insert into #tmpTable values( ' + @TableName + ' , ' + @Count + ' ) ')
fetch next from cur into @TableName
end
close cur
deallocate cur
select sum(DCount) from #tmpTable
drop table #tmpTable
运行的时候却发现报错，请哪位高手帮忙解决下，谢谢！
declare @TableName varchar(100),@Count int
declare cur cursor for select table_name from information_schema.tables
create table #tmpTable (TblName nvarchar(100), DCount int)
open cur
fetch next from cur into @TableName
while @@fetch_status=0
begin
exec( ' select count(*) into ' + @Count + ' from ' + @TableName )
exec( 'insert into #tmpTable values( ' + @TableName + ' , ' + @Count + ' ) ')
fetch next from cur into @TableName
end
close cur
deallocate cur
select sum(DCount) from #tmpTable
drop table #tmpTable
运行的时候却发现报错，请哪位高手帮忙解决下，谢谢！
教你个简单方法
select
object_Name(ID) as tableName,rows
from sysindexes
where indid<=1
or
以上语句改为
declare @TableName varchar(100),@Count int,@sql nvarchar(2000)--改为nvarcar,因为要动态传参sp_executesql
declare cur cursor for select table_name from information_schema.tables
create table #tmpTable (TblName nvarchar(100), DCount int)
open cur
fetch next from cur into @TableName
while @@fetch_status=0
begin
set @sql=N' select @Count=count(*) from [' + @TableName+']'
exec sp_executesql @sql,N'@Count int output',@Count output
exec( 'insert into #tmpTable values( ''' + @TableName + ''' , ' + @Count + ' ) ')--这里加上引号
fetch next from cur into @TableName
end
close cur
deallocate cur
select sum(DCount) from #tmpTable
drop table #tmpTable