查询sql server中所有数据表的记录行数与表空间大小的代码(推荐)
本文导语: 1,查询所有数据表的记录行数,逆序排列 代码示例: 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 r...
1,查询所有数据表的记录行数,逆序排列
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.
2,查询所有数据表所占的空间大小,逆序排列
#---------------------
--主要原理:
--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 --这二个要加上,好多教程中都没加,加上后才可以更多的库,要不必须手工删除掉临时表后才能继续。
您可能感兴趣的文章:
统计sql server用户数据表大小(记录总数和空间占用情况)的代码
Sql server 2005 查询数据库中所有表的记录行数的代码
sql 查询所有表的记录数的三种实现方法