-- 统计某个表的空间大小, 行数信息 EXEC Your_DB.dbo.sp_spaceused 'dbo.t_log'; -- 统计多个数据库多个表的空间大小, 行数信息 create table tmp_table_space(table_name varchar(50), table_rows int, total_size varchar(20),data_size varchar(20), index_size varchar(20), unused_size varchar(20), dbname varchar(30)); insert into tmp_table_space(table_name,table_rows,total_size,data_size,index_size,unused_size) EXEC Your_DB.dbo.sp_spaceused 'dbo.t_log'; insert into tmp_table_space(table_name,table_rows,total_size,data_size,index_size,unused_size) EXEC Your_DB.dbo.sp_spaceused 'dbo.t_log2'; update tmp_table_space set dbname='AHBZMJ' where dbname is null; /************************************************************************* 用于查看对应数据库的大小、占用空间以及该数据库中各个系统表、用户表 使用方法： 在查询分析器中选择您要查看的数据库，然后运行此代码即可。 **************************************************************************/ ----新建一个表spt_result_table存储数据库中各个表的空间信息 if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[spt_result_table]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[spt_result_table] GO create table spt_result_table ( tablename varchar(776) null, ----表名 rows varchar(776) null, ----表中现有的行数 reserved varchar(776) null, ----为表保留的空间总量 data varchar(776) null, ----表中的数据所使用的空间量 indexp varchar(776) null, ----表中的索引所使用的空间量 unused varchar(776) null ----表中未用的空间量 )ON [PRIMARY] GO ----创建存储过程prc_database_spaceused：计算数据库大小及各个表占用空间的情况 if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[prc_database_spaceused]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[prc_database_spaceused] GO create procedure prc_database_spaceused as BEGIN declare @id int declare @type character(2) declare @pages int declare @dbname sysname ----数据库名 declare @dbsize dec(15,0) ----数据库大小 declare @logsize dec(15) declare @bytesperpage dec(15,0) declare @pagesperMB dec(15,0) declare @objname varchar(776) ----记录表名 declare @database_size varchar(776) declare @unallocated_space varchar(776) select @dbname = db_name() ----数据库为当前数据库 create table #spt_space ( rows int null, reserved dec(15) null, ----保留的空间总量 data dec(15) null, ----数据使用的空间总量 indexp dec(15) null, ----索引使用的空间 unused dec(15) null ----未用的空间量 ) ---- 计算数据大小(以kB页为单位) select @dbsize = sum(convert(dec(15),size)) from dbo.sysfiles where (status & 64 = 0) ---- 计算日志大小(以kB页为单位) select @logsize = sum(convert(dec(15),size)) from dbo.sysfiles where (status & 64 <> 0) ---- 求得一个page有多少bytes select @bytesperpage = low from master.dbo.spt_values where number = 1 and type = 'E' ---- 计算MB占多少page（MB = 1048576B） select @pagesperMB = 1048576 / @bytesperpage ---- 计算数据库大小 set @database_size = ltrim(str((@dbsize + @logsize) / @pagesperMB,15,2) + ' MB') ---- 计算未用的空间量的大小 set @unallocated_space = ltrim(str((@dbsize - (select sum(convert(dec(15),reserved)) from sysindexes where indid in (0, 1, 255) )) / @pagesperMB,15,2)+ ' MB') ---- 保留的空间总量 insert into #spt_space (reserved) select sum(convert(dec(15),reserved)) from sysindexes where indid in (0, 1, 255) select @pages = sum(convert(dec(15),dpages)) from sysindexes where indid < 2 select @pages = @pages + isnull(sum(convert(dec(15),used)), 0) from sysindexes where indid = 255 ---- 数据使用的空间总量 update #spt_space set data = @pages ---- 索引使用的空间 update #spt_space set indexp = (select sum(convert(dec(15),used)) from sysindexes where indid in (0, 1, 255))- data ---- 未用的空间量 update #spt_space set unused = reserved - (select sum(convert(dec(15),used)) from sysindexes where indid in (0, 1, 255)) ---- 输出数据库大小信息 select database_name = @dbname, database_size = @database_size, unallocated_space = @unallocated_space, reserved = ltrim(str(reserved * d.low / 1024.,15,0) + ' ' + 'KB'), data = ltrim(str(data * d.low / 1024.,15,0) + ' ' + 'KB'), index_size = ltrim(str(indexp * d.low / 1024.,15,0) + ' ' + 'KB'), unused = ltrim(str(unused * d.low / 1024.,15,0) + ' ' + 'KB') from #spt_space, master.dbo.spt_values d where d.number = 1 and d.type = 'E' ---- 清空临时表#spt_space delete from #spt_space -----定义游标，计算表大小信息 declare cur_table cursor for select name from sysobjects where xtype = 'U' or xtype = 'S' order by xtype asc -----打开游标 open cur_table fetch next from cur_table into @objname while (@@fetch_status = 0) begin select @id = null select @id = id, @type = xtype from sysobjects where id = object_id(@objname) --dbcc updateusage(0,@objname) with no_infomsgs insert into #spt_space (reserved) select sum(reserved) from sysindexes where indid in (0, 1, 255) and id = @id select @pages = sum(dpages) from sysindexes where indid < 2 and id = @id select @pages = @pages + isnull(sum(used), 0) from sysindexes where indid = 255 and id = @id update #spt_space set data = @pages update #spt_space set indexp = (select sum(used) from sysindexes where indid in (0, 1, 255) and id = @id) - data update #spt_space set unused = reserved - (select sum(used) from sysindexes where indid in (0, 1, 255) and id = @id) update #spt_space set rows = i.rows from sysindexes i where i.indid < 2 and i.id = @id insert into spt_result_table select tablename = object_name(@id), rows = convert(char(11), rows), reserved = ltrim(str(reserved * d.low / 1024.,15,0) + ' ' + 'KB'), data = ltrim(str(data * d.low / 1024.,15,0) + ' ' + 'KB'), index_size = ltrim(str(indexp * d.low / 1024.,15,0) + ' ' + 'KB'), unused = ltrim(str(unused * d.low / 1024.,15,0) + ' ' + 'KB') from #spt_space, master.dbo.spt_values d where d.number = 1 and d.type = 'E' truncate table #spt_space fetch next from cur_table into @objname end close cur_table deallocate cur_table select * from spt_result_table where tablename is not null; drop table #spt_space END GO exec prc_database_spaceused ---- 删除spt_result_table表 if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[spt_result_table]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[spt_result_table] GO ---- 删除存储过程prc_database_spaceused if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[prc_database_spaceused]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[prc_database_spaceused] GO
方法二：表明和行数
SELECT a.name, b.rows
FROM sysobjects AS a INNER JOIN
sysindexes AS b ON a.id = b.id
WHERE (a.type = 'u') AND (b.indid IN (0, 1))
ORDER BY b.rows DESC