- -- 统计某个表的空间大小, 行数信息
- 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
|