原貼位置：http://blog.csdn.net/minisunny/article/details/1791760
declare @sql varchar(8000),@count int,@step int
set nocount on
--@step越大運(yùn)行速度越快，但如果太大會(huì)造成生成的sql字符串超出限制導(dǎo)致語句不完整出錯(cuò)
--建議為50
set @step = 50
if object_id(N'tempdb.db.#temp') is not null
drop table #temp
create table #temp (name sysname,count numeric(18))
if object_id(N'tempdb.db.#temp1') is not null
drop table #temp1
create table #temp1 (id int identity(1,1),name sysname)
insert into #temp1(name)
select name from sysobjects where xtype = 'u';
set @count = @@rowcount while @count>0
begin
set @sql = ''
select @sql = @sql + ' select ''' + name + ''',count(1) from ' + name + ' union'
from #temp1 where id > @count - @step and id <= @count
set @sql = left(@sql,len(@sql) - len('union'))
insert into #temp exec (@sql)
set @count = @count - @step
end
select count(count) 總表數(shù),sum(count) 總記錄數(shù) from #temp
select * from #temp order by count,name
set nocount off
經(jīng)過[測試],該方法可以通過,不過有時(shí)候@step的值需要手動(dòng)設(shè)置一下,@step=50應(yīng)該就可以滿足大部分數(shù)據(jù)庫的需要了.如果表名都比較短的話,可以設(shè)置@step=80或者100.
create table #(id int identity ,tblname varchar(50),num int)
declare @name varchar(30)
declare roy cursor for select name from sysobjects where xtype='U'
open roy
fetch next from roy into @name
while @@fetch_status=0
begin
declare @i int
declare @sql nvarchar(1000)
set @sql='select @n=count(1) from '+@name
exec sp_executesql @sql,N'@n int output',@i output
insert into # select @name,@I
fetch next from roy into @name
end
close roy
deallocate roy
select * from #
該方法用到了游標(biāo),如果數(shù)據(jù)庫表很多的話速度可能會(huì)比較慢,但是該表不受表名長短影響,對(duì)所有數(shù)據(jù)庫都適用.
最后一種方法是利用隱藏未公開的系統(tǒng)存儲(chǔ)過程sp_MSforeachtable
CREATE TABLE #temp (TableName VARCHAR (255), RowCnt INT)
EXEC sp_MSforeachtable 'INSERT INTO #temp SELECT ''?'', COUNT(*) FROM ?'
SELECT TableName, RowCnt FROM #temp ORDER BY TableName
DROP TABLE #temp
