查询sql server中所有数据表的记录行数与表空间大小的代码(推荐)

select a.name AS name,b.rows AS rows  INTO #Temp1
from gongkongnet.dbo.sysobjects a LEFT JOIN  gongkongnet.dbo.sysindexes b
ON( a.id=b.id ) WHERE  a.type='u'  ORDER BY b.rows DESC
 
SELECT top 20 name,max(rows) as rows FROM #Temp1 GROUP BY NAME ORDER BY max(rows) DESC
 
DROP TABLE #Temp1

---搜集整理 www.jb200.com

#---------------------
--主要原理：
--exec sp_spaceused '表名' --取得表占用空間
--exec sp_spaceused ''--數據庫所有空間
---以上二个exec代码要注释掉，这里只是说明，并非真正的执行代码
 
create table #Data(name varchar(100),row varchar(100),reserved varchar(100),data varchar(100),index_size varchar(100),unused varchar(100))
 
declare @name varchar(100)
declare cur cursor  for
    select name from sysobjects where xtype='u' order by name
open cur
fetch next from cur into @name
while @@fetch_status=0
begin
    insert into #data
    exec sp_spaceused   @name
    print @name
 
    fetch next from cur into @name
end
close cur
deallocate cur
 
create table #DataNew(name varchar(100),row int,reserved int,data int,index_size int,unused int)
 
insert into #dataNew
select name,convert(int,row) as row,convert(int,replace(reserved,'KB','')) as reserved,convert(int,replace(data,'KB','')) as data,
convert(int,replace(index_size,'KB','')) as index_size,convert(int,replace(unused,'KB','')) as unused from #data 
 
select * from #dataNew order by data desc 

drop table #data
drop table #dataNew  --这二个要加上，好多教程中都没加，加上后才可以更多的库，要不必须手工删除掉临时表后才能继续。