SQL Server 查看数据库表使用空间 系统表

sp_spaceused (Transact-SQL)   

系统表  

select * from sys.databases

select * from sys.sysdatabases

select * from sys.tables where [name] = 'sys_car'

select * from INFORMATION_SCHEMA.TABLES where TABLE_NAME = 'sys_car'

select * from sys.objects where type = 'U' and type_desc = 'USER_TABLE'

select * from sys.syscolumns where id=object_id('sys_car') and name='CarName'

select * from sys.extended_properties where minor_id = 0

select * from sys.indexes WHERE type_desc != 'HEAP'


USE [数据库名称]
GO

-- 数据库文件路径
select * from sys.database_files
select * from sys.master_files

select * from sys.database_mirroring

select * from sys.database_recovery_status

查看某个数据库所有表

USE [dbTest]
GO

SELECT * FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
ORDER BY TABLE_NAME

查看某个数据库所有索引

USE [dbTest]
GO

SELECT
    i.name AS IndexName,
    OBJECT_NAME(i.object_id) AS TableName,
    i.index_id,
    i.type_desc,
    i.is_primary_key,
    i.is_unique,
    i.is_disabled
FROM sys.indexes AS i
INNER JOIN sys.tables AS t ON i.object_id = t.object_id
WHERE i.type_desc != 'HEAP'
ORDER BY TableName,IndexName;

-- 查询某个表的所有索引
-- exec sp_helpindex [表名]
exec sp_helpindex test

查看【数据库】总大小 和 可用空间

-- 方式 1：查看数据库文件的大小和使用情况
SELECT 
    name AS [FileName],
	type_desc AS [File Type],
    size/128 AS [SizeInMB],
	size/128.0 AS [Current Size in MB 当前大小(MB)],
    FILEPROPERTY(name, 'SpaceUsed')/128.0 AS [Space Used In MB 已用空间(MB)],
    (size - FILEPROPERTY(name, 'SpaceUsed'))/128.0 AS [Available Space In MB 可用空间(MB)]
FROM sys.database_files
WHERE 1=1 
-- AND type_desc = 'LOG';
go

-- 方式 2：查看数据库文件的大小和使用情况
SELECT 
    name AS 'File Name', 
    type_desc AS 'File Type',
	size/128 AS [SizeInMB],
    size/128.0 AS 'Current Size in MB 当前大小(MB)',
	FILEPROPERTY(name, 'SpaceUsed')/128.0 AS [Space Used In MB 已用空间(MB)],
    size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS [Available Space In MB 可用空间(MB)],
	database_id
FROM sys.master_files
WHERE database_id = DB_ID() 
--AND type_desc = 'LOG';
go

-- 查看数据库文件路径，当前大小
SELECT DB_NAME(database_id) AS [Database Name],
[Name] AS [Logical Name],
[Physical_Name] AS [Physical Name],
((size * 8) / 1024) AS [Size(MB)]
FROM sys.master_files
ORDER BY [Size(MB)] DESC
go

-- 存储过程
EXEC sp_spaceused; 
-- unallocated space 未分配空间，可用空间
-- reserved          预留空间

-- 返回值
-- database_name：数据库名称。
-- database_size：数据库大小 (MB)。 database_size 包括数据和日志文件。
-- unallocated space：数据库中未为数据库对象保留的空间。

-- reserved：由数据库中对象分配的空间总量。
-- data：数据使用的空间总量。
-- index_size：索引使用的空间总量。
-- unused：为数据库中的对象保留但尚未使用的空间总量。
-- rows：表中现有的行数。 如果指定的对象是 Service Broker 队列，则此列指示队列中的消息数。
go

每个表的已用空间

-- 方式 1
SELECT db_name() as DbName,
    t.NAME AS TableName,
    s.Name AS SchemaName,
    p.rows AS RowCounts,
	SUM(a.total_pages) * 8 AS TotalSpaceKB, 
    SUM(a.used_pages) * 8 AS UsedSpaceKB, 
    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB
FROM sys.tables t
INNER JOIN sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN sys.schemas s ON t.schema_id = s.schema_id
WHERE t.NAME NOT LIKE 'dt%' AND t.is_ms_shipped = 0 AND i.OBJECT_ID > 255 
GROUP BY t.Name, s.Name, p.Rows
ORDER BY UsedSpaceKB DESC;
go
 
-- 方式 2
SELECT db_name() as DbName,
    t.NAME AS TableName,
    s.Name AS SchemaName,
    p.rows AS RowCounts,
    SUM(a.total_pages) * 8 AS TotalSpaceKB, 
    CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS 总共占用空间MB,
    SUM(a.used_pages) * 8 AS UsedSpaceKB, 
    CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS UsedSpaceMB, 
    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB,
    CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS UnusedSpaceMB
FROM sys.tables t
INNER JOIN sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN sys.schemas s ON t.schema_id = s.schema_id
WHERE t.NAME NOT LIKE 'dt%' AND t.is_ms_shipped = 0 AND i.OBJECT_ID > 0
GROUP BY t.Name, s.Name, p.Rows
ORDER BY 总共占用空间MB desc;
go
 
-- 方式 3 临时表，索引空间
CREATE TABLE #TableSizes
(
    TableName NVARCHAR(128),     -- 表名称
    RowCounts BIGINT,            -- 表中的总行数
    ReservedSpace NVARCHAR(128), -- 预留空间（总的）
    DataSpace NVARCHAR(128),     -- 数据占用的空间
    IndexSpace NVARCHAR(128),    -- 索引占用的空间
    UnusedSpace NVARCHAR(128)    -- 未使用的空间
)
 
EXEC sp_MSforeachtable @command1="INSERT INTO #TableSizes EXEC sp_spaceused '?'"
 
SELECT TableName
,RowCounts
,ReservedSpace
,UnusedSpace
,DataSpace
,IndexSpace
FROM #TableSizes ORDER BY CAST(REPLACE(ReservedSpace, ' KB', '') AS INT) DESC
 
DROP TABLE #TableSizes
go
 
-- 方式4 存储过程
-- unallocated space 未分配空间，可用空间
-- reserved          预留空间
EXEC sp_spaceused;
 
EXEC sp_spaceused 'test';
 
go

*
*
*