EXEC sys.[sp_spaceused] 的行数经过两次封装
EXEC sys.[sp_spaceused] 的行数经过两次封装
EXEC sys.[sp_spaceused] @objname = N'', -- nvarchar(776) @updateusage = '' -- varchar(5)
SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS OFF GO create procedure sys.sp_spaceused --- 2003/05/19 14:00 @objname nvarchar(776) = null, -- The object we want size on. @updateusage varchar(5) = false -- Param. for specifying that -- usage info. should be updated. as declare @id int -- The object id that takes up space ,@type character(2) -- The object type. ,@pages bigint -- Working variable for size calc. ,@dbname sysname ,@dbsize bigint ,@logsize bigint ,@reservedpages bigint ,@usedpages bigint ,@rowCount bigint /* ** Check to see if user wants usages updated. */ if @updateusage is not null begin select @updateusage=lower(@updateusage) if @updateusage not in ('true','false') begin raiserror(15143,-1,-1,@updateusage) return(1) end end /* ** Check to see that the objname is local. */ if @objname IS NOT NULL begin select @dbname = parsename(@objname, 3) if @dbname is not null and @dbname <> db_name() begin raiserror(15250,-1,-1) return (1) end if @dbname is null select @dbname = db_name() /* ** Try to find the object. */ SELECT @id = object_id, @type = type FROM sys.objects WHERE object_id = object_id(@objname) -- Translate @id to internal-table for queue IF @type = 'SQ' SELECT @id = object_id FROM sys.internal_tables WHERE parent_id = @id and internal_type = 201 --ITT_ServiceQueue /* ** Does the object exist? */ if @id is null begin raiserror(15009,-1,-1,@objname,@dbname) return (1) end -- Is it a table, view or queue? IF @type NOT IN ('U ','S ','V ','SQ','IT') begin raiserror(15234,-1,-1) return (1) end end /* ** Update usages if user specified to do so. */ if @updateusage = 'true' begin if @objname is null dbcc updateusage(0) with no_infomsgs else dbcc updateusage(0,@objname) with no_infomsgs print ' ' end set nocount on /* ** If @id is null, then we want summary data. */ if @id is null begin select @dbsize = sum(convert(bigint,case when status & 64 = 0 then size else 0 end)) , @logsize = sum(convert(bigint,case when status & 64 <> 0 then size else 0 end)) from dbo.sysfiles select @reservedpages = sum(a.total_pages), @usedpages = sum(a.used_pages), @pages = sum( CASE -- XML-Index and FT-Index and semantic index internal tables are not considered "data", but is part of "index_size" When it.internal_type IN (202,204,207,211,212,213,214,215,216,221,222,236) Then 0 When a.type <> 1 and p.index_id < 2 Then a.used_pages When p.index_id < 2 Then a.data_pages Else 0 END ) from sys.partitions p join sys.allocation_units a on p.partition_id = a.container_id left join sys.internal_tables it on p.object_id = it.object_id /* unallocated space could not be negative */ select database_name = db_name(), database_size = ltrim(str((convert (dec (15,2),@dbsize) + convert (dec (15,2),@logsize)) * 8192 / 1048576,15,2) + ' MB'), 'unallocated space' = ltrim(str((case when @dbsize >= @reservedpages then (convert (dec (15,2),@dbsize) - convert (dec (15,2),@reservedpages)) * 8192 / 1048576 else 0 end),15,2) + ' MB') /* ** Now calculate the summary data. ** reserved: sum(reserved) where indid in (0, 1, 255) ** data: sum(data_pages) + sum(text_used) ** index: sum(used) where indid in (0, 1, 255) - data ** unused: sum(reserved) - sum(used) where indid in (0, 1, 255) */ select reserved = ltrim(str(@reservedpages * 8192 / 1024.,15,0) + ' KB'), data = ltrim(str(@pages * 8192 / 1024.,15,0) + ' KB'), index_size = ltrim(str((@usedpages - @pages) * 8192 / 1024.,15,0) + ' KB'), unused = ltrim(str((@reservedpages - @usedpages) * 8192 / 1024.,15,0) + ' KB') end /* ** We want a particular object. */ else begin /* ** Now calculate the summary data. * Note that LOB Data and Row-overflow Data are counted as Data Pages for the base table * For non-clustered indices they are counted towards the index pages */ SELECT @reservedpages = SUM (reserved_page_count), @usedpages = SUM (used_page_count), @pages = SUM ( CASE WHEN (index_id < 2) THEN (in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count) ELSE 0 END ), @rowCount = SUM ( CASE WHEN (index_id < 2) THEN row_count ELSE 0 END ) FROM sys.dm_db_partition_stats WHERE object_id = @id; /* ** Check if table has XML Indexes or Fulltext Indexes which use internal tables tied to this table */ IF (SELECT count(*) FROM sys.internal_tables WHERE parent_id = @id AND internal_type IN (202,204,207,211,212,213,214,215,216,221,222,236)) > 0 BEGIN /* ** Now calculate the summary data. Row counts in these internal tables don't ** contribute towards row count of original table. */ SELECT @reservedpages = @reservedpages + sum(reserved_page_count), @usedpages = @usedpages + sum(used_page_count) FROM sys.dm_db_partition_stats p, sys.internal_tables it WHERE it.parent_id = @id AND it.internal_type IN (202,204,207,211,212,213,214,215,216,221,222,236) AND p.object_id = it.object_id; END SELECT name = OBJECT_NAME (@id), rows = convert (char(20), @rowCount), reserved = LTRIM (STR (@reservedpages * 8, 15, 0) + ' KB'), data = LTRIM (STR (@pages * 8, 15, 0) + ' KB'), index_size = LTRIM (STR ((CASE WHEN @usedpages > @pages THEN (@usedpages - @pages) ELSE 0 END) * 8, 15, 0) + ' KB'), unused = LTRIM (STR ((CASE WHEN @reservedpages > @usedpages THEN (@reservedpages - @usedpages) ELSE 0 END) * 8, 15, 0) + ' KB') end return (0) -- sp_spaceused GO
/*
** Now calculate the summary data.
* Note that LOB Data and Row-overflow Data are counted as Data Pages for the base table
* For non-clustered indices they are counted towards the index pages
*/
再封装
SELECT * FROM sys.dm_db_partition_stats
SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS OFF GO CREATE VIEW sys.dm_db_partition_stats AS SELECT c.partition_id, i.object_id, i.index_id, c.partition_number, c.in_row_data_page_count, c.in_row_used_page_count, c.in_row_reserved_page_count, c.lob_used_page_count, c.lob_reserved_page_count, c.row_overflow_used_page_count, c.row_overflow_reserved_page_count, c.used_page_count, c.reserved_page_count, c.row_count FROM sys.indexes$ i CROSS APPLY OpenRowSet(TABLE PARTITIONCOUNTS, i.object_id, i.index_id, i.rowset) c GO
c.row_count
sys.indexes$
oracle的数据字典视图
例如 v$instance可以查询有关实例的信息
用DAC连接sqlserver,查询sys.indexes$表的内容
SELECT * FROM sys.indexes$
不用DAC连接进去的话会报对象名无效
消息 208,级别 16,状态 1,第 1 行 对象名 'sys.indexes$' 无效。
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 浏览器原生「磁吸」效果!Anchor Positioning 锚点定位神器解析
· 没有源码,如何修改代码逻辑?
· 一个奇形怪状的面试题:Bean中的CHM要不要加volatile?
· [.NET]调用本地 Deepseek 模型
· 一个费力不讨好的项目,让我损失了近一半的绩效!
· 微软正式发布.NET 10 Preview 1:开启下一代开发框架新篇章
· 没有源码,如何修改代码逻辑?
· PowerShell开发游戏 · 打蜜蜂
· 在鹅厂做java开发是什么体验
· WPF到Web的无缝过渡:英雄联盟客户端的OpenSilver迁移实战