SQL SERVER 查询所有表 统计每张表的大小

查询某数据库中的所有数据表

SELECT name as table_name
FROM sysobjects
WHERE xtype='u'
ORDER BY name

查询某数据库中的所有数据表及其数据总条数

SELECT  a.name 数据表,
        b.rows 数据总条数,
        RTRIM(8 * b.dpages) + ' KB' 占用空间大小 
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.dpages DESC,b.rows DESC;

查询数据库文件路径信息

select  * from sys.database_files

查询数据库文件大小

--当前数据库服务器所有数据库文件大小
SELECT DB_NAME(database_id) AS DatabaseName,
Name AS Logical_Name,
Physical_Name, (size*8.0)/1024 SizeMB
FROM sys.master_files
GO
 
--查看某一个数据库的文件大小
SELECT DB_NAME(database_id) AS DatabaseName,
Name AS Logical_Name,
Physical_Name, (size*8.0)/1024 SizeMB
FROM sys.master_files
WHERE DB_NAME(database_id) = 'database数据库名'
GO

SQL SERVER 统计每张表的大小

建存储过程

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- 说明：更新查询数据库中各表的大小，结果存储到数据表中
-- =============================================

CREATE PROCEDURE [dbo].[P_GetTableSpaceInfo]

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 P_GetTableSpaceInfo
SELECT * FROM temp_tableSpaceInfo;