SQLServer查看所有表大小、表行数和占用空间信息 - 牧人池塘 - DBA杂谈录

2021-10-13

暂无评论

SQLServer查看所有表大小、表行数和占用空间信息

一、查看表名和对应的数据行数

select  a.name as '表名',b.rows as '表数据行数'
from sysobjects a inner join sysindexes b
on a.id = b.id
where   a.type = 'u'
and b.indid in (0,1)
--and a.name not like 't%'
order by b.rows desc

二、查看表名和表占用空间信息

--判断临时表是否存在，存在则删除重建
if exists(select 1 from tempdb..sysobjects where id=object_id('tempdb..#tabName') and xtype='u')
drop table #tabName
go
create table #tabName(
tabname varchar(100),
rowsNum varchar(100),
reserved varchar(100),
data varchar(100),
index_size varchar(100),
unused_size 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 #tabName
    exec sp_spaceused @name
    --print @name
 
    fetch next from cur into @name
end
close cur
deallocate cur

select tabname as '表名',rowsNum as '表数据行数',reserved as '保留大小',data as '数据大小',index_size as '索引大小',unused_size as '未使用大小'
from #tabName
--where tabName not like 't%'
order by cast(rowsNum as int) desc

--系统存储过程说明：

--sp_spaceused 该存储过程在系统数据库master下。
exec sp_spaceused '表名' --该表占用空间信息
exec sp_spaceused          --当前数据库占用空间信息

三、查询数据库大小：
先打开要查询的数据库，执行sql如下：

EXEC sp_spaceused @updateusage = N'TRUE';

结果是两个表，第一个表中包含了基本的统计信息，第二个表示更加详细的数据占用情况

或者用如下自定义方法：

-- =============================================
-- 描  述：更新查询数据库中各表的大小，结果存储到数据表中
-- =============================================
CREATE PROCEDURE [dbo].[sp_UpdateTableSpaceInfo]
AS
BEGIN
    --查询是否存在结果存储表
    IF NOT EXISTS (SELECT * FROM sysobjects where id = OBJECT_ID(N'temp_tableSpaceInfo') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
    BEGIN
        --不存在则创建
        CREATE TABLE temp_tableSpaceInfo
        (name NVARCHAR(128), 
        rows char(11), 
        reserved VARCHAR(18),
        data VARCHAR(18),
        index_size VARCHAR(18),
        unused VARCHAR(18))
    END
    --清空数据表
    DELETE FROM temp_tableSpaceInfo

    --定义临时变量在遍历时存储表名称
    DECLARE @tablename VARCHAR(255)

    --使用游标读取数据库内所有表表名
    DECLARE table_list_cursor CURSOR FOR 
    SELECT name FROM sysobjects 
    WHERE OBJECTPROPERTY(id, N'IsTable') = 1 AND name NOT LIKE N'#%%' ORDER BY name

    --打开游标
    OPEN table_list_cursor
    --读取第一条数据
    FETCH NEXT FROM table_list_cursor INTO @tablename 

    --遍历查询到的表名
    WHILE @@FETCH_STATUS = 0
    BEGIN
        --检查当前表是否为用户表
        IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(@tablename) AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
        BEGIN
            --当前表则读取其信息插入到表格中
            EXECUTE sp_executesql N'INSERT INTO temp_tableSpaceInfo EXEC sp_spaceused @tbname', N'@tbname varchar(255)', @tbname = @tablename
        END
        --读取下一条数据
        FETCH NEXT FROM table_list_cursor INTO @tablename 
    END

    --释放游标
    CLOSE table_list_cursor
    DEALLOCATE table_list_cursor
END

GO

用的时候可以这样执行：

EXEC sp_UpdateTableSpaceInfo
SELECT * FROM temp_tableSpaceInfo

Tag标签:「空间 SQLServer 表大小表行数」更新时间:「2021-11-03 16:40:40」阅读次数:「3041」

相关博文：